[SOLVED] Week of Year Function

January 20, 2012, 04:01 PM

I am trying to find the Week of Year if the business uses Fiscial Year not Calendar Year.

Example we start our Fiscial Year on 10/01/2011.

I can get the Week of the Year using calendar year.

-SET &WEEK_OF_YEAR = HPART(HDTTM(DATECVT(&MDYY, I8MDYY, MDYY), 14, 'HMDYYs'), 'WEEK', 'I2');

I need to know how to make the tool think 10/01/2011 is the start of the first week not 01/01/2012.

Thank You,

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

January 20, 2012, 08:11 PM

Dan Satchell

There are some functions for determining fiscal year and fiscal quarter: FIYR, FIQTR, and FIYYQ (described in this new features document: New Features). They may be available in your release. But as yet, there is no function for determining fiscal week.

You might try adding the number of days between 10/1/2011 and 1/1/2012 to your date and then perform the HPART function to get the week. This approach assumes that fiscal year 2012 begins on 10/1/2011.

This message has been edited. Last edited by: Dan Satchell,

January 22, 2012, 12:26 AM

Doug

Try doing an offset (MyFiscalYearBeg = DATEDIF ... (of jan 1st next year) - 2 months)... You'll need to do a year check to see when to use next year instead of this year.

January 23, 2012, 09:35 AM

jgelona

If it were me, I'd create a DEFINE FUNCTION for this where the input is the date. Something like this:

DEFINE FUNCTION WK_OF_FY(INDATE/A8YYMD)
  INDATE1/YYMD=INDATE;
  INMTH/M=INDATE1;
  INYEAR/YY=INDATE1;
  FY/I4=IF INMTH LT 10 THEN INYEAR-1 ELSE INYEAR;
  BFY/YYMD=DATECVT(FY*10000+1001,'I8YYMD','YYMD');
  DAYS/I6=INDATE1-BFY;
  WK_OF_FY/I2=(DAYS/7)+1;
END
-RUN

Place this in your edasprof and you can do the followng:

-SET &INDATE=20120123;
-SET &WEEKFY=WK_OF_FY(&INDATE);
-TYPE Date=&INDATE  Week of FY:&WEEKFY
-RUN

and you get this:

Date=20120123 Week of FY:17

No matter what date you pass, you will get the week of the fiscal year for that date.

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