Focwizzes:

Our fiscal year starts on June 1 and ends on May 31 (you can guess why this topic came up now ...).

I have in the past devised a couple of ways to define the fiscal year, but I'm not proud of my efforts - kinda clunky IMHO.

So I'm wondering if maybe someone has a spiffy way of defining the fiscal in maybe a line or two. It's just dawned on me that maybe I could use the fact that our fiscal starts nicely on the third quarter and maybe I could use that ...

(Yes, I know I should buy that 1000 ways book - I promise I'll order it today!)

All suggestions are welcome!

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

Will 2 lines do?

-SET &YEAR = EDIT(&MDYY, '$$$$$$99');
-SET &FISCAL = IF &MDYY GT '05999999' THEN &YEAR ELSE &YEAR - 1;

A Time Dimension table is very useful for this kind of thing. It would be comprised of a Date key and facts about the date, eg. Fiscal Year, Fiscal Quarter, Day of Week and whatever else you require. You join to this table and immediately get Date related information without DEFINE or COMPUTE statements. I've generated data for a table like this by using data created for other projects. I can share this, the data will have to be adjusted as our Fiscal Year runs November to October.

Try this:

DEFINE FILE CAR                                               
MYNEW/YYMD WITH COUNTRY = 'MAY 30 2011' ;                     
MYNEW = IF COUNTRY EQ 'ENGLAND' THEN MYNEW + 3 ELSE MYNEW ;   
                                                              
MYI/I8YYMD WITH COUNTRY = MYNEW ;                             
MYYR/I4 = MYI/10000 ;                                         
FISYR/I4 = IF(MYI - MYYR*10000) GT 531 THEN MYYR+1 ELSE MYYR; 
END                                                           
TABLE FILE CAR                                                
PRINT MYNEW MYI MYYR FISYR                                    
END                                                             

You may want MYYR-1 instead of MYYR+1

You'll need to do this a lot so I don't think sticking DEFINEs in all your code is an elegant solution.

Another suggestion is to "create temporary fields that are independent of either a Master File or a request using the DEFINE FUNCTION command.

(Documentation: Creating Reports With WebFOCUS Language > Creating Temporary Fields > Creating Temporary Fields Independent of a Master File)

Here's an example of code that you INCLUDE where required. It can be used by Dialogue Manager or report requests. (You have to modify this to reflect your Fiscal Year and Fiscal Quarters).

-*------------------------------------------------------------------------------
-* Module Name   : deffunc_fiscala.fex
-* Description   : User DEFINE Function for Fiscal Quarter and Fiscal Year
-* Developed by  : Francis Mariani
-* Date Developed: August 4, 2008
-* Notes         : Input a Date in YYMD alpha format, depending on which
-*                 function is called, outputs:
-*                   Beginning of the Fiscal Quarter
-*                   End of the Fiscal Quarter
-*                   Beginning of the Fiscal Year
-*                   End of the Fiscal Year
-*
-* Notes         : Call the functions in the following manner:
-*                 Dialogue Manager:
-*                   -INCLUDE DEFFUNC_FISCAL
-*                   -SET &DT_BOFQ = BOFQ(&YYMD);
-*                 WebFOCUS:
-*                   -INCLUDE DEFFUNC_FISCAL
-*                   DEFINE FILE FILENAME
-*                   DT_BOFQ = BOFQ(DATE_FIELD);
-*                   END
-*------------------------------------------------------------------------------

DEFINE FUNCTION BOFQ(INDATE/A8)
INDTYYA/A4 = EDIT(INDATE,'9999$$$$');
INDTYY/I4  = EDIT(INDTYYA);
OUTDTYY/I4 = INDTYY - 1;
INDTMD/A4  = EDIT(INDATE,'$$$$9999');
BOFQ/A8    =
  IF INDTMD FROM '1101' TO '1231' THEN INDTYYA || '1101' ELSE
  IF INDTMD FROM '0101' TO '0131' THEN EDIT(OUTDTYY) || '1101' ELSE
  IF INDTMD FROM '0201' TO '0430' THEN INDTYYA || '0201' ELSE
  IF INDTMD FROM '0501' TO '0731' THEN INDTYYA || '0501' ELSE
  IF INDTMD FROM '0801' TO '1031' THEN INDTYYA || '0801' ELSE INDATE;
END
-RUN

DEFINE FUNCTION EOFQ(INDATE/A8)
INDTYYA/A4 = EDIT(INDATE,'9999$$$$');
INDTYY/I4  = EDIT(INDTYYA);
INDTMD/A4  = EDIT(INDATE,'$$$$9999');
OUTDTYY/I4 = INDTYY + 1;
EOFQ/A8    =
  IF INDTMD FROM '1101' TO '1231' THEN EDIT(OUTDTYY) || '0131' ELSE
  IF INDTMD FROM '0101' TO '0131' THEN INDTYYA || '0131' ELSE
  IF INDTMD FROM '0201' TO '0430' THEN INDTYYA || '0430' ELSE
  IF INDTMD FROM '0501' TO '0731' THEN INDTYYA || '0731' ELSE
  IF INDTMD FROM '0801' TO '1031' THEN INDTYYA || '1031' ELSE INDATE;
END
-RUN

DEFINE FUNCTION BOFY(INDATE/A8)
INDTYYA/A4 = EDIT(INDATE,'9999$$$$');
INDTYY/I4  = EDIT(INDTYYA);
INDTMM/A2  = EDIT(INDATE,'$$$$99$$');
OUTDTYY/I4 = IF INDTMM LT '11' THEN INDTYY - 1 ELSE INDTYY;
BOFY/A8    = EDIT(OUTDTYY) || '1101';
END
-RUN

DEFINE FUNCTION EOFY(INDATE/A8)
INDTYYA/A4 = EDIT(INDATE,'9999$$$$');
INDTYY/I4  = EDIT(INDTYYA);
INDTMM/A2  = EDIT(INDATE,'$$$$99$$');
OUTDTYY/I4 = IF INDTMM LT '11' THEN INDTYY ELSE INDTYY + 1;
EOFY/A8    = EDIT(OUTDTYY) || '1031';
END
-RUN