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,
-SET &YEAR = EDIT(&MDYY, '$$$$$$99');
-SET &FISCAL = IF &MDYY GT '05999999' THEN &YEAR ELSE &YEAR - 1;
Robert F. Bowley Jr. Owner TaRa Solutions, LLC
In WebFOCUS since 2001
June 02, 2011, 11:05 AM
Francis Mariani
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.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
June 02, 2011, 11:11 AM
Edward Wolfgram
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
IBI Development
June 02, 2011, 11:23 AM
Francis Mariani
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
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server