Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Use Max of value in Condition

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Use Max of value in Condition
 Login/Join
 
Master
posted
I want to report QuantitySinceInception, FiscalYearToDate and MonthToDate below by location.

I have tried this numerous ways. It works as shown below but the user is prompted for the current Fiscal Year and Period. I’d like to eliminate that but I am not sure how best to do it…nothing has worked so far.

How can I alter the code below to simply find and compare the max fiscal year and period in the data?

DEFINE FILE MYDATA
FYTD/D12=IF FISCALYEAR EQ &CurrentFiscalYear THEN QTY ELSE 0;
FMTD/D12=IF FISCALYEAR EQ &CurrentFiscalYear AND FYMTHNUMBER = &CurrentFiscalMonthNumber THEN QTY ELSE 0;
ITD/D12 = QTY;
END
TABLE FILE MYDATA
SUM
ITD AS 'Since, Inception'
FYTD AS 'Fiscal Year, To Date'
FMTD AS 'Fiscal Month, To Date'
BY LOWEST LOCATION
HEADING
"Appointment Count Summary By Location:"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END

This message has been edited. Last edited by: <Kathryn Henning>,


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Virtuoso
posted Hide Post
You say the user is being prompted for the Fiscal Year and Period and that you want to eliminate that so I must assume you want your &CurrentFiscalYear and &CurrentFiscalMonthNumber variables to be determined at runtime based on your current data. If so, you can try something like this:

-* Capture max. Fiscal Year and Month Number available
TABLE FILE MYDATA
SUM
        MAX.FISCALYEAR AS 'CurrentFiscalYear'
        MAX.FYMTHNUMBER AS 'CurrentFiscalMonthNumber'
ON TABLE HOLD AS HMAXDATA
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SET ASNAMES ON
END
-RUN
-READFILE HMAXDATA

-* Get measurements for the most current year and period
DEFINE FILE MYDATA
FYTD/D12=IF FISCALYEAR EQ &CurrentFiscalYear THEN QTY ELSE 0;
FMTD/D12=IF FISCALYEAR EQ &CurrentFiscalYear AND FYMTHNUMBER EQ &CurrentFiscalMonthNumber THEN QTY ELSE 0;
ITD/D12 = QTY;
END
TABLE FILE MYDATA
SUM 
ITD AS 'Since, Inception'
FYTD AS 'Fiscal Year, To Date'
FMTD AS 'Fiscal Month, To Date'
BY LOWEST LOCATION
HEADING
"Appointment Count Summary By Location:"
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Master
posted Hide Post
thanks, I actually saw a similar post and tried something like this...though I don't fully understand what the READFILE does...

However, I had the same issue I have after running the above code...it is still prompting me for the two values instead of subsituting them from the HMAXDATA file.


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Master
posted Hide Post
I hust noticed, after the prompts appear, its throwing an error on the DEFINES:

0 ERROR AT OR NEAR LINE 64 IN PROCEDURE zrftstnewdatapullii
(FOC260) AN OPERATION IS MISSING AN ARGUMENT
0 ERROR AT OR NEAR LINE 65 IN PROCEDURE zrftstnewdatapullii
(FOC266) IF .. THEN .. ELSE .. SYNTAX ERROR
0 ERROR AT OR NEAR LINE 66 IN PROCEDURE zrftstnewdatapullii
(FOC260) AN OPERATION IS MISSING AN ARGUMENT
0 ERROR AT OR NEAR LINE 71 IN PROCEDURE zrftstnewdatapullii
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: ITD


I must be referencing the filed from HMAXDATA incorrectly...?


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Master
posted Hide Post
njsden's solution - as well as the original post - seems way more complex than required, unless I miss the point of the question.

Robert - I suggest:

1) Check out the WF built-in function for beginning of Fiscal Year. That's your starting point.

For the most recent - ie. current fiscal year and month all you have to do is:

DEFINE FILE MYDATA
CURRYEAR/YY='&YYMD';
CURRMONTH/M='&YYMD';
CURRYRMONTH/YYM='&YYMD';  (optional but useful)
END


With one or more of those there isn't any need to use MAX - all you have to do is something along the lines of adding something like one of thes options to your define:

QTY/D12 = IF CURRYRMONTH GE FISCALYRMONTH THEN QTY ELSE 0;

or

QTY/D12 = IF CURRYEAR GE FISCALYR AND CURRMONTH LE FYMONTHNUMBER THEN QTY ELSE 0;

One way or another the amper variables aren't needed at all.

The /YYM date format is extremely helpful in these sorts of situations. I use it all the time.


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
Master
posted Hide Post
if I follow this you are trying to use an IBI varaiable containing the year and period...But our FY runs Jul-Jun and Jul-period1, Jun=period 12...not sure this will work for me.


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Platinum Member
posted Hide Post
quote:
-* Capture max. Fiscal Year and Month Number available
TABLE FILE MYDATA
SUM
MAX.FISCALYEAR AS 'CurrentFiscalYear'
MAX.FYMTHNUMBER AS 'CurrentFiscalMonthNumber'
ON TABLE HOLD AS HMAXDATA
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SET ASNAMES ON
END
-RUN
-READFILE HMAXDATA


I believe the -READFILE command only works in 7.7 and above so if you are still on 7.6 you will need to do

-READ &yr.I4. &mth.I2.

Another thing if your data set contains more than one year of data MAX.FYMTHNUMBER will always return June since that is the max value of month in your fiscal year, you want the max month in the max fiscal year.

This may be more appropriate in that circumstance:

TABLE FILE MYDATA
SUM
MAX.FYMTHNUMBER
BY HIGHEST 1 FISCALYEAR
ON TABLE HOLD AS HMAXDATA
END
-RUN
-READ HMAXDATA &CurrentFiscalYear.I4. &CurrentFiscalMonthNumber.I2.

You may need to change to the formats (I4,I2) to match your own data


WF 7.6.11
Output: HTML, PDF, Excel
 
Posts: 123 | Location: UK | Registered: October 09, 2003Report This Post
Virtuoso
posted Hide Post
quote:
it is still prompting me for the two values


That's probably because your procedure sits in the MRE and has the "Prompt for parameters" enabled. Just locate our procedure, right-click it and select "Properties". Look for the "Prompt for parameters" checkbox and uncheck it.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Virtuoso
posted Hide Post
quote:
njsden's solution - as well as the original post - seems way more complex than required, unless I miss the point of the question.


I think the complexity lies in the fact that Robert wants to limit his report to the most recent Fiscal data available in his table which may or may not be related to the current system date; that's why &YYMD and other derived variables wouldn't help in this particular case, or at least that's what I understood from his original post.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Master
posted Hide Post
quote:
That's probably because your procedure sits in the MRE and has the "Prompt for parameters" enabled. Just locate our procedure, right-click it and select "Properties". Look for the "Prompt for parameters" checkbox and uncheck it.

Tried that...it errored out at the &CurrentFiscalYear code


Re: I think the complexity lies in the fact that Robert wants to limit his report to the most recent Fiscal data available in his table which may or may not be related to the current system date; that's why &YYMD and other derived variables wouldn't help in this particular case, or at least that's what I understood from his original post.


Exactly!


Prod/Dev: WF Server 7.7.05M/Win 2008 - WF Client 7.7.05/Win 2008 - Dev. Studio: 7.7.05/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 7.7.05 /Windows 7 (Local) Output: Mostly HTML and EXL2K


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Expert
posted Hide Post
This Link creates & and && values for the fiscal year and period number. Modify to access your database, or, CAR file and it will work.

Copy the code into a fex and -INCLUDE at the top of your program...

To use, here is your code with their use(May or May Not need quotes, depends on the format of FISCALYEAR and FYMNTNUMBER):

  
DEFINE FILE MYDATA
FYTD/D12=IF FISCALYEAR EQ '&&FCN.EVAL' THEN QTY ELSE 0;
FMTD/D12=IF FISCALYEAR EQ '&&FCN.EVAL' AND FYMTHNUMBER EQ '&&PCN.EVAL' THEN QTY ELSE 0;
ITD/D12 = QTY;
END
TABLE FILE MYDATA
SUM 
ITD AS 'Since, Inception'
FYTD AS 'Fiscal Year, To Date'
FMTD AS 'Fiscal Month, To Date'
BY LOWEST LOCATION
HEADING
"Appointment Count Summary By Location:"
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END


hth


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Master
posted Hide Post
thanks Tom...I will give this a try.


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Master
posted Hide Post
not sure this is really what I need.We have a few going to training soon. I will send this as an example and hopefully they will return with a solution. One would think there would be a way to calculate a value and use it in a procedure.

Thanks all for the tips!


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Master
posted Hide Post
quote:
I want to report QuantitySinceInception, FiscalYearToDate and MonthToDate below by location.


Forgive me if I'm flogging a dead horse, but if that is really what you want the answer lies somewhere in the area I suggested above. I do all kinds of reports based on YearToDate and MonthToDate and get the endpoint (i.e. today's date) from the OS.

It makes no difference if there doesn't happen to be any Quantity for the past month, or two months - all you do is check for the existence of a Quantity and if the last month with a quantity was, say, September, then you can set September as your end date.

As for fiscal years, you can either use the built-in functions or construct your own in a few lines of code.

The following HOLD file follows a MATCH FILE sequence. YRMTH is a /YYM field derived from a Bill of Lading date. Our fiscal year starts on June 1.

DEFINE FILE HOLD
    YR/YY = YRMTH;
    MTH/M = YRMTH;
    MTH2/I2 = IF MTH LE 5 THEN (MTH + 7) ELSE (MTH - 5);
    MONTH/MT = YRMTH;
    YR1/YY = YRMTH;
    YR2/YY = IF MTH LE 05 THEN (YR1 -1) ELSE YR1;
    YR3/YY = YR2 + 1;
    YEAR/A11 = EDIT(YR2) | ' - ' | EDIT(YR3);
END


One small benefit of this approach over the built-in functions is that you have various year and month parameters available for a subsequent TABLE request:

TABLE FILE HOLD
PRINT
     'HOLD.HOLD.TOTCOMMSLS' AS ' COMMISSION,    FOB,   MONTHLY'
     COMPUTE CUMFOB/D13.2 = IF MONTH EQ 'JUN' THEN TOTCOMMSLS ELSE TOTCOMMSLS + LAST CUMFOB;
 AS ' COMMISSION ,    FOB, CUMULATIVE'
     'HOLD.HOLD.TOTCOMM' AS ' COMMISSION,   INCOME,   MONTHLY'
     COMPUTE CUMCOMM/D13.2 = IF MONTH EQ 'JUN' THEN TOTCOMM ELSE TOTCOMM + LAST CUMCOMM;
.
.
.
.
.

BY 'HOLD.HOLD.YEAR' NOPRINT
BY 'HOLD.HOLD.MTH2' NOPRINT
BY 'HOLD.HOLD.MONTH'

ON HOLD.HOLD.YEAR SUBTOTAL
     'HOLD.HOLD.TOTCOMMSLS'
     'HOLD.HOLD.TOTCOMM'
     'HOLD.HOLD.TOTDIRSLS'
     'HOLD.HOLD.PROFIT'
     'HOLD.HOLD.GROSSINC'
     'HOLD.HOLD.EARNINGS'
     'HOLD.HOLD.EXPENSE'
     NETP AS ''
ON HOLD.HOLD.YEAR PAGE-BREAK
.
.
.

This message has been edited. Last edited by: George Patton,


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
Master
posted Hide Post
..not beating a dead horse....right now the user is prompted for CURRENT FISCAL YEAR and CURRENT MONTH via the DEFINES in palce. All works well...so long as the user enters the appropriate values.....

I have no problem creating a hold file that determines the Current Fiscal Year and Current Fiscal Month and ends up with a single row, 2 columns with the value for each.

The problem is substituting those hold file values into the DEFINE that follows.

Going back to my original code, I'd need to substitute HOLD.CurrentFY and HOLD.CurrentFMo for
&CurrentFiscalYear and &CurrentFiscalMonthNumber respectively.

I can not get this to work. I tried the READFILE suggestion..did not work. I think there answer is likely something very simple involving getting the code set up correctly.

For now I am simply running the code below...allowing the user to be prompted.

DEFINE FILE WALKINDATA
FYTD/D12=IF WALKINDATA.WALKINDA.FISCALYEAR EQ &CurrentFiscalYear THEN WALKINDATA.WALKINDA.APPTS ELSE 0;
FMTD/D12=IF WALKINDATA.WALKINDA.FISCALYEAR EQ &CurrentFiscalYear AND WALKINDATA.WALKINDA.PERIOD = &CurrentPeriod THEN WALKINDATA.WALKINDA.APPTS ELSE 0;
ITD/D12=IF WALKINDATA.WALKINDA.FISCALYEAR LE &CurrentFiscalYear THEN WALKINDATA.WALKINDA.APPTS ELSE 0;
END

TABLE FILE WALKINDATA
SUM
WALKINDATA.WALKINDA.LOCATIONNAME
ITD AS 'Since, Inception'
FYTD AS 'Fiscal Year, To Date'
FMTD AS 'Fiscal Month, To Date'
BY LOWEST WALKINDATA.WALKINDA.LOCATION
HEADING
"Appointment Count Summary By Location:"
"Inception thru &CurrentPeriod"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
TYPE=REPORT,
GRAPHCOLOR='GREEN',
.
.
.


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Virtuoso
posted Hide Post
quote:
I tried the READFILE suggestion..did not work.

Hey Robert, I had suggested using -READFILE to get the values into &variables you can later use to implement your filters but did not pay attention to the fact that you're using WebFOCUS 7.6 and not 7.7 as already pointed out by Tewy, so you'll have to use -READ instead to capture each variable (just look at Tewy's sample code) making sure your ON TABLE HOLD uses FORMAT ALPHA.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Master
posted Hide Post
Robert,

The following line contains an error for starters:

WALKINDATA.WALKINDA.PERIOD = &CurrentPeriod - it should be " EQ " not " = "

quote:

FMTD/D12=IF WALKINDATA.WALKINDA.FISCALYEAR EQ &CurrentFiscalYear AND WALKINDATA.WALKINDA.PERIOD = &CurrentPeriod THEN WALKINDATA.WALKINDA.APPTS ELSE 0;


Is the issue that you can't figure out the current fiscal year?

In the following I:

-* get current year and month
-* assume June 1 is the beginning of fiscal year and you are defining fiscals by the beginning rather than the end date
-* strip out fully-qualified fieldnames to make it more readable


DEFINE FILE WALKINDATA
CURR_MTH/M=&YYMD;
CURR_YEAR/YY=&YYMD;
CURR_FISCAL_YEAR/YY = IF CURR_MTH LE 05 THEN (CURR_YEAR -1) ELSE CURR_YEAR;

FYTD/D12=IF FISCALYEAR EQ CURR_FISCAL_YEAR THEN APPTS ELSE 0;
FMTD/D12=IF FISCALYEAR EQ CURR_FISCAL_YEAR AND PERIOD EQ CURR_MTH THEN APPTS ELSE 0;

-* The following line is not necessary since you want all appointments to date without condition
-* ITD/D12=IF FISCALYEAR LE &CurrentFiscalYear THEN APPTS ELSE 0;
END

TABLE FILE WALKINDATA
SUM 
 LOCATIONNAME
-* ITD AS 'Since, Inception'
 APPTS AS 'Since, Inception'
 FYTD AS 'Fiscal Year, To Date'
 FMTD AS 'Fiscal Month, To Date'

-* BY LOWEST isn't doing anything - it's assumed
-* BY LOWEST WALKINDATA.WALKINDA.LOCATION

BY WALKINDATA.WALKINDA.LOCATION
HEADING
"Appointment Count Summary By Location:"
"Inception thru &CurrentPeriod"
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
 INCLUDE = endeflt,
$
TYPE=REPORT,

-*  hmmm.... the following line looks fishy to me - you are doing a TABLE request but this references a GRAPH
-*  but I don't have much experience with graphs so no comment ..
 GRAPHCOLOR='GREEN',
...etc
ENDSTYLE
END


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
Gold member
posted Hide Post
The most likely reason why it was still prompting for the parameters is because there was no -SET setting them to a value.

Another option you could do that doesn't use parameters is

TABLE FILE WALKINDATA
SUM
MAX.FISCALYEAR AS 'CURRENTFISCALYEAR'
MAX.PERIOD AS 'CURRENTPERIOD'
PRINT *
ON TABLE HOLD AS WALKIN_HOLD
ON TABLE SET ASNAMES ON
END

DEFINE FILE WALKIN_HOLD
FYTD/D12=IF FISCALYEAR EQ CURRENTFISCALYEAR THEN APPTS ELSE 0;
FMTD/D12=IF FISCALYEAR EQ CURRENTFISCALYEAR AND PERIOD EQ CURRENTPERIOD THEN APPTS ELSE 0;
ITD/D12=IF FISCALYEAR LE CURRENTFISCALYEAR THEN APPTS ELSE 0;
END

TABLE FILE WALKIN_HOLD
SUM 
LOCATIONNAME
ITD AS 'Since, Inception'
FYTD AS 'Fiscal Year, To Date'
FMTD AS 'Fiscal Month, To Date'
BY LOCATION
HEADING
"Appointment Count Summary By Location:"
"Inception thru <CURRENTPERIOD"
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt, 
.
.
.

This message has been edited. Last edited by: Crymsyn,


WF: 8201, OS: Windows, Output: HTML, PDF, Excel
 
Posts: 78 | Registered: November 08, 2010Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Use Max of value in Condition

Copyright © 1996-2020 Information Builders