I'm sure this topic has been discussed but I don't know how to properly search for it. So please forgive me I did try searching before posting this question.

I'm running a FY report that will run based on the current date. For example if the report is generated before July 1st, the range of data will be 07/1/(year-2) through 06/30/(year-1)
If report is generated on or after July 1st, the range of data will be 07/1(year-1) through 06/30(year)

Does anyone have an example of how this should be written?

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

Try this code

/
-SET &FISCAL_MMDDE = '0630' ;
-SET &FISCAL_MMDDB = '0701' ;
-SET &FISCAL_MTH = '07' ;
-SET &CURRENT_MMDD = EDIT(&MDY,'9999') ;
-SET &CURRENT_MTH = EDIT(&MDY,'99') ;
-SET &CURRENT_DATE= EDIT(&MDY,'99') || '/' || EDIT(&MDY,'$$99') || '/' || &THIS_YY ;
-SET &FISCAL_YEAR = IF &CURRENT_MMDD LE &FISCAL_MMDDE THEN &THIS_YY -1 ELSE &THIS_YY ;
-SET &FISCAL_YEAR1 = &FISCAL_YEAR -1 ;
-SET &FISCAL_YEAR2 = &FISCAL_YEAR -2 ;
-SET &FISCAL_MDYY = &CURRENT_MMDD || &FISCAL_YEAR ;
-SET &FISCAL_YYMD = &FISCAL_YEAR || &CURRENT_MMDD;
-SET &FISCAL_BEG = EDIT(&FISCAL_MMDDB,'99') || '/' || EDIT(&FISCAL_MMDDB,'$$99') || '/' || &FISCAL_YEAR ;
-SET &FISCAL_BEG1 = EDIT(&FISCAL_MMDDB,'99') || '/' || EDIT(&FISCAL_MMDDB,'$$99') || '/' || &FISCAL_YEAR1 ;
-SET &FISCAL_BEG2 = EDIT(&FISCAL_MMDDB,'99') || '/' || EDIT(&FISCAL_MMDDB,'$$99') || '/' || &FISCAL_YEAR2 ;
-SET &FISCAL_END1 = EDIT(&FISCAL_MMDDE,'99') || '/' || EDIT(&FISCAL_MMDDE,'$$99') || '/' || &FISCAL_YEAR ;
/

Also check out the FIYR, FIQTR and FIYYQ functions.

T

You might want to check out (almost) 1001 Ways to Work with Dates in WebFocus -- Best doc out on the topic - either book form or PDF --

You can find it at www.aviter.com.

Vivian

The book is extremely valuable even though it could do with a serious redesign. It would help to employ the concept of white space, and put related items on the same or opposite pages, etc. It would be a lot more pages, but much more readable.

I have to run reports on State and Federal Fiscal Years.

The state fiscal year (SFY) is like yours, July-June. The federal fiscal year (FFY) is Oct-Sep.

I use the following DEFINE FUNCTION to take the desired date and return the appropriate fiscal year.

-* File getfy.fex
DEFINE FUNCTION GETFY(INDATE/A8YYMD,YRTYP/A1)
-*
-*  For YRTYP S, get SFY for INDATE (Default)
-*  For YRTYP F, get FFY for INDATE
-*
  INDATE1/YYMD=INDATE;
  INMTH/M=INDATE1;
  INYEAR/YY=INDATE1;
  YRTYP=IF NOT YRTYP IN ('F','S') THEN 'S';
  GETFY/I4=
    IF YRTYP EQ 'F' AND INMTH LT 10 OR YRTYP EQ 'S' AND INMTH LT 7
      THEN INYEAR ELSE INYEAR+1;
END
-RUN

Because I use INDATE as A8YYMD format, I can use this anywhere.

Once I have the year, computing the begin and end dates for the year are simple.

-SET &FY=GETFY(&YYMD,'S');
-SET &EFY=&FY*10000+701;
-SET &BEG=&EFY-10000;
-SET &EFY=AYMD(&EFY,-1,'I8YYMD');

If this is run today (20150827), then &FY is 2016, &EFY is 20160701, &BFY is 20150701, then subtracting 1 day from &EFY leaves it as 20160630.