[SOLVED] YTD report and scheduling issue?

I guess you can say I'm a newbie with WebFOCUS and have come across the below scenario for which I'm not sure how to solution.

-Report is scheduled via report caster to run monthly on the 1st day to show ytd data.

-In report I set current year and previous month
-SET &CURR_DATE = &YYMD;
-SET &CURR_YEAR = EDIT(&CURR_DATE, '99990101');
-SET &PREV_MONTH = DATECVT(DATEADD(DATECVT(&YYMD,'I8YYMD','YYMD'),'D',-1), 'YYMD', 'I8YYMD');

-I query records where open time is greater than or equal to current year and less than or equal to previous month.
( PROBSUMMARYM1.PROBSUMMARYM1.OPENTIMECONVERT GE CURRENTYEAR ) AND ( PROBSUMMARYM1.PROBSUMMARYM1.OPENTIMECONVERT LE PREVIOUSMONTH )

-Everything runs fine until January 1st comes around. On January 1st I would want the report to do what it did for the previous months but I don't think it's going to work as current year will be 2014 and previous month will be set to 20131231.

- Any suggestions on how to accommodate the year changing and getting YTD results?

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

WebFOCUS 7.6
Windows, All Outputs

Posts: 13 | Registered: August 22, 2013

Silver Member

posted

Hide Post

Calculate the beginning of the year you use in the filter (you call it &curr_year) based off the previous month, instead of calculating both using today (&YYMD).

-SET &LAST_MTD=DATECVT((DATEADD((DATECVT(&YYMD,'I8YYMD','YYMD')),M,-1)),'YYMD','I8YYMD');
-SET &LAST_EOM=DATECVT((DATEMOV((DATECVT(&LAST_MTD.EVAL,'I8YYMD','YYMD')),'EOM')),'YYMD','I8YYMD');
-SET &BOY=DATECVT((DATEMOV((DATECVT(&LAST_EOM,'I8YYMD','YYMD')),'BOY')),'YYMD','I8YYMD');
-TYPE &LAST_MTD
-TYPE &LAST_EOM
-TYPE &BOY

You can experiment with how different dates are calculated by setting TESTDATE

 SET TESTDATE = 20140101
-RUN
-SET &LAST_MTD=DATECVT((DATEADD((DATECVT(&YYMD,'I8YYMD','YYMD')),M,-1)),'YYMD','I8YYMD');
-SET &LAST_EOM=DATECVT((DATEMOV((DATECVT(&LAST_MTD.EVAL,'I8YYMD','YYMD')),'EOM')),'YYMD','I8YYMD');
-SET &BOY=DATECVT((DATEMOV((DATECVT(&LAST_EOM,'I8YYMD','YYMD')),'BOY')),'YYMD','I8YYMD');
-TYPE &LAST_MTD
-TYPE &LAST_EOM
-TYPE &BOY

Good ones to try are: March 1 in a regular year, March 1 in a leap year, the beginning of the year, and a few extra dates.

WF: WebFocus 7.7.03
Data: Oracle, MSSQL, DB2
OS: Windows
Output: HTML/AHTML,PDF,EXL2K FORMULA, COMT

Posts: 43 | Registered: November 21, 2011

Ignored post by nd posted

Show Post

Tonyperkis

Member

posted

Hide Post

Thanks, helpful for getting different dates but do I do the logic in the Where expression?

My where clause is currently

( ACTIVITYM1.ACTIVITYM1.OPERATOR IN ('user1','user2','user3') ) AND ( ACTIVITYM1.ACTIVITYM1.TYPE EQ 'Reopen' ) AND ( ACTIVITYM1.ACTIVITYM1.DATESTAMP GE CURR_YEAR ) AND (ACTIVITYM1.ACTIVITYM1.DATESTAMP LE PREV_MONTH)

Do I need to incorporate something like this?

IF PREVMONTHMOD = '12' THEN (ACTIVITYM1.ACTIVITYM1.DATESTAMP GE PREVYEAR) AND (ACTIVITYM1.ACTIVITYM1.DATESTAMP LE PREV_MONTH) ELSE (ACTIVITYM1.ACTIVITYM1.DATESTAMP GE CURR_YEAR) AND (ACTIVITYM1.ACTIVITYM1.DATESTAMP LE PREV_MONTH)

If so I not sure of the syntax.

WebFOCUS 7.6
Windows, All Outputs

Posts: 13 | Registered: August 22, 2013

Ignored post by Tonyperkis posted

Show Post

Silver Member

posted

Hide Post

Using the example variable names above, your filter would be

 
( ACTIVITYM1.ACTIVITYM1.OPERATOR IN ('user1','user2','user3') ) AND ( ACTIVITYM1.ACTIVITYM1.TYPE EQ 'Reopen' ) 
AND ( ACTIVITYM1.ACTIVITYM1.DATESTAMP GE &BOY ) 
AND (ACTIVITYM1.ACTIVITYM1.DATESTAMP LE &LAST_EOM)

&LAST_MTD subtracts one month from the current date. Subtracting a month (instead of 30 days) means that FOCUS handles the issue of months being different lengths for you.

&LAST_EOM takes the date calculated in &LAST_MTD, and determines what the end-of-month date is for that date.

&BOY takes the date calculated in &LAST_EOM and determines what the first day of the year is based on that date.

You don't need to branch your logic based on whether a month is December or not. You will just ask it for the date greater than the calculated BOY through the EOM for last month.

On Jan 1, 2014, those values will be interpreted like this:

 LAST_MTD: 20131201
 LAST_EOM: 20131231
 BOY: 20130101

Whereas if the job ran today (Nov 5, 2013), you'd get this:

LAST_MTD: 20131005
 LAST_EOM: 20131031
 BOY: 20130101

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

WF: WebFocus 7.7.03
Data: Oracle, MSSQL, DB2
OS: Windows
Output: HTML/AHTML,PDF,EXL2K FORMULA, COMT

Posts: 43 | Registered: November 21, 2011

Ignored post by nd posted

Show Post

Tonyperkis

Member

posted

Hide Post

Thank you for clearing that up. Totally makes sense now and I unserstand it. Thanks again for the help.

WebFOCUS 7.6
Windows, All Outputs

Posts: 13 | Registered: August 22, 2013

Ignored post by Tonyperkis posted

Show Post

Please Wait. Your request is being processed...