9 replies to this topic

[andykay]

A posting has been made requesting help from ALL at the ProIVRC on how to calculate the fiscal week number of a passed in date.

So I propose, to all the creative minds out there, a friendly challenge, to see all the different ways all of us programmers might approach this task if it were given to us.

The Challenge: How many lines of code would it take you to calculate this week number, from start to finish, with the finish being a UMSG giving the answer.

Remember that a company's fiscal calendar starts on the same day of every year but not necessarily on January 1st, and Logic Comments or spaces for ease of following don't count in the total number of lines. Only actual code lines count. Use @DATE as the Date In Question, and any date you want as the date your Fiscal Year begins.

Good Luck.

I've got mine down to 27 lines of logic.

---------------------------------------------------------------------------------------------------------------

#DATE_TO_CHECK_AGAINST = @DATE
#CHECK_YEAR = CDATE(#DATE_TO_CHECK_AGAINST,’YYYY’)

$FISCAL_BEGIN_DATE = ‘04/15/’
$CHECK= $FISCAL_BEGIN_DATE + CONV(#CHECK_YEAR)
#CHECK = CDATE($CHECK,’MM/DD/YYYY’)
IF #CHECK > #DATE_TO_CHECK_AGAINST THEN
#CHECK_YEAR -= 1
ENDIF

$NEW_FISCAL_YR_BEGIN_DATE = $FISCAL_BEGIN_DATE + CONV(#CHECK_YEAR)
#NEW_FISCAL_YR_BEGIN_DATE = CDATE($NEW_FISCAL_YR_BEGIN_DATE,’MM/DD/YYYY’)

// THE FOLLOWING ASSUMES THAT SUNDAY IS THE LAST DAY OF THE WEEK
// AND THAT THE FIRST DAY OF THE YEAR FALLS IN WEEK 1 OF THE YEAR
// OTHERWISE MAKE ADJUSTMENTS ACCORDINGLY

$DAY_OF_WEEK = CDATE(#NEW_FISCAL_YR_BEGIN_DATE,’DDD’)

CASE $DAY_OF_WEEK
WHEN ‘MON’: #OFFSET = 6
WHEN ‘TUE’: #OFFSET = 5
WHEN ‘WED’: #OFFSET = 4
WHEN ‘THU’: #OFFSET = 3
WHEN ‘FRI’: #OFFSET = 2
WHEN ‘SAT’: #OFFSET = 1
WHEN ‘SUN’: #OFFSET = 0
ENDCASE

#CHECK_DATE = #NEW_FISCAL_YR_BEGIN_DATE + #OFFSET
#WEEK = 1

WHILE #CHECK_DATE <= #DATE_TO_CHECK_AGAINST
#WEEK += 1
#CHECK_DATE += 7
ENDWHILE

UMSG(CDATE(#DATE_TO_CHECK_AGAINST,’D, M DD, YYYY’) + ‘ IS IN WEEK NUMBER ‘ + CONV(#WEEK),-1)

[Rick San Soucie]

#OFFSET = (INDEX($D_O_W,'SATFRITHUWEDTUEMON') + 2) /3

This replaces your 9 lines for the CASE statement. It isn't good coding style, but if the contest is the least number of lines....

[Chris Pepper]

Ummm.... am I missing something? I'm assuming &#FISCAL contails the ProIV internal date for the start of the Fiscal Year.

#WEEK = INT(( @DATE - &#FISCAL) / 7 ) + 1
UMSG( 'DATE TO CHECK IS: ' + CDATE(@DATE,'D, M DD, YYYY) + ' IS IN WEEK NUMBER : ' + CONV(#WEEK),-1)

[Chris Pepper]

After my morning drink I decided that that using &#FISCAL was outside the rules, as you just supplied the start month and day. So I need to add the code to calculate the value I used in &#FISCAL - which is effectively the first part of your code.

$CHECK_YEAR = CDATE(@DATE, 'YYYY')
$FISCAL_BEGIN = '04/15'
$CHECK_DATE = $CHECK_YEAR + $FISCAL_BEGIN(1,2) + $FISCAL_BEGIN(4,5)

IF CDATE($CHECK_DATE) < @DATE THEN
$CHECK_YEAR = PIC( CONV($CHECK_YEAR) - 1, '9999')
$CHECK_DATE = $CHECK_YEAR + $FISCAL_BEGIN(1,2) + $FISCAL_BEGIN(4,5)
ENDIF

then the original code...
#WEEK = INT(( @DATE - CDATE($CHECK_DATE)) / 7 ) + 1
UMSG( 'DATE TO CHECK IS: ' + CDATE(@DATE,'D, M DD, YYYY) + ' IS IN WEEK NUMBER : ' + CONV(#WEEK),-1)

Edited by Chris Pepper, 28 April 2006 - 08:16 AM.

[andykay]

After my morning drink I decided that that using &#FISCAL was outside the rules, as you just supplied the start month and day.

Chris,

Any way you can get the beginning date of the fiscal year into the equation is valid. I just hard coded it because I had no idea how the person who originally made the inquiry about how to calculate the fiscal week keeps their value for the beginning date, and in my haste it completely slipped my mind that most companies do keep the fiscal year start date somewhere as a GV.

Bravo, Chris.

[Chris Pepper]

Yes I agree with your analysis, but my experience of week numbers is that they only make sense if they refer to equal periods of 7 days. Otherwise if you are trying to produce comparable statistics on a weekly basis (e.g. sales, expenses, salaries), which is the purpose of such a measure, it only makes sense if they are of equal length.

The reason that these do not start on a fixed date (e.g. Jan 1, April 5) is because they need to start on the first day of a week. Note that internationally there is no standard "first" day of the week, so the start day may depend on the location of the company. Regardless, the weeks should be of equal length.

[andykay]

Chris is correct.

While a calendar year can begin on any day of the week, a fiscal year usually always begins on the first day of the week, of some week, of a particular month. More specifically, it ends on the Sun closest to the end of the prior week whether that day falls before or after the beginning of the fiscal year beginning and starts that following Monday.

For Example:

If we use the first week in February as the start of the Fiscal Year, you'd get...

FY
------
2004: Start Mon Feb 2nd, 2004
2004: End Sun Jan 30th, 2005

2005: Start Mon Jan31st, 2005
2005: End Sun Jan 29th, 2006

2006: Start Mon Jan 30th, 2006
2006: End Sun Jan 28th, 2007


Had I known this when I posted this challenge, I could've significantly trimmed the fat by excluding the OFFSET logic and might have came to a different solution than the logic I used.

[Bob Filipiak]

Gents,

Allow me to throw in another wrinkle!

I look at the challenge differently, if I have a fiscal year, say for example that starts on April 1 and runs through March 30, and I use fiscal weeks, what is the first fiscal week of the year that begins on April 1, 2004.

My question is, is fiscal week 1 of that fiscal year (April 1, 2004 to March 30, 2005) April 1 - 3 or is it April 4 -10??? If it is April 4 - 10, then what fiscal week are the dates April 1-3 a part of? fiscal year while you are at it?

A little research indicates that ISO standard 8601 deals with date/time standards, but not having the opportunity to examine that standard thoroughly, I wonder if the definition of a fiscal week has not already been proposed.

Perhaps someone who is more familiar with ISO 8601 can enlighten us

Bob Filipiak

[Steven Torrance]

ISO8601 has information on week numbers that states something along the lines of:

"A week begins with a Monday, and the first week of a year is the one which includes the first Thursday"!!!!

i.e. find the first Thursday in January and that is week 1. Note that this can mean that if 1st Jan is a Thursday, week1 begins on 29th December the previous year! It can also start as late as 4th Jan.

I had to handle this, but also some strange week numbers from Middle East (where everything seems to be run off the new moon rising), so ended up storing the date along with a week number in a file for lookup.