I am having great difficulty in calculating our Fiscal Week Number. Our fiscal new year begins Sept. 01. Our fiscal week starts on Sunday and ends on Saturday. Any Ideas, Solutions?
Thanks.
j_perdzock (BOB member since 2008-02-04)
Create a Calendar dimension in a database somewhere. You can then use this to define associated day, week and month numbers. Also enables you to define working days/bank holidays (just be careful how you design it if it’s for multiple countries). You can populate the database in a number of ways but the easiest is to source it from a spreadsheet (gives you total control to sepcify the attributes of each day without resorting to complex/convoluted calculations).
gpn01 
(BOB member since 2004-11-30)
to_char(add_months(sysdate()-1, 4), ‘YYYY-WW’)
The -1 is the adjustment for the Sunday vs. Monday and the +4 months moves a September to December. Something along those lines…
Werner Daehn 
(BOB member since 2004-12-17)
I think you would only want to add 3 months…
- Ernie
eepjr24 
(BOB member since 2005-09-16)
edit: No, four months. 1st Sept → Oct, Nov, Dec → 1st Jan. Makes four months, doesn’t it?
Werner Daehn 
(BOB member since 2004-12-17)
That’s pretty convenient because the Day in Week function in DI returns 1 for Sunday and 7 for Saturday. The Week in Year works on a Sunday to Saturday basis, too.
I’ve done a similar things for Sales(Sat-Fri) and Manufacturing(Mon-Sun) weeks in year, in FY, for data from SAP. What I do is find the week in calendar year and apply an offset, starting with Week_in_Year(Calendar_Date). I needed an approach where I could just find the first week (Sales, Manufacturing) week in year based on days, then take its calendar week in year for an offset calc.
Assuming that Sept 1 is always in week 1, this year for example that means Week in Year = 36. So you can treat all remaining Weeks as (Week in Year - 35) for the current Calendar Year (35 instead of 36 because we don’t have a Zero week). Week 37 is FY Week 2, 38 is FY week 3, etc.
All you need to know is what the Calendar week of FY Week 1 was for the rest of the year processing.
When the calendar year flips, at the end of the last fiscal week from 2008, you have to add the value of the max week in year where year = 2008. Week 1 in 2009 = Max Fiscal Week of 2008 + 1.
This may sound convoluted, but it’s not. The trick is handling the year transition gracefully for any year, w/o custom exceptions. There’s no way around the fact that once you cross the year line, you need to allow for it. It’s not a one rule logical problem, unless you reduce it to actually reading days in week, going from Sun-Sat and every Sunday increment the week by one until you reach next Sept 01. That’s pretty brute force but also a simple alternative.
For example if I determine using add months, that’s going to have issues when I get to January in my Fiscal Year. I cannot just add 4 months to Sysdate-1. Try plugging January 2009 into this expression and see what you get:
to_char(add_months(sysdate()-1, 4), ‘YYYY-WW’)
You also have to of course check each row to ensure you’ve not reached end of FY current.
2009 01 + 4 months set into week format will give a lower number for “WW” the year started with. It will also give 2009 when possibly FY 2008 started in Sept (rare, but possible).
Just store the offset if it’s not clear, along with Day in Week so you can see the pattern matches Sun-Sat, and Week in Cal Year plus Week in Fiscal Year so you can see the offset is correct. If it helps, mock this up in a spreadsheet first so you have reference data for comparison.
Stracy 
(BOB member since 2006-07-14)
I really get beaten on that one, don’t I? 
Werner Daehn (BOB member since 2004-12-17)
Had the same problem this week …
Resolved it using an SQL Server function adapter to Data services (Credit: http://www.sqlteam.com/article/returning-a-week-number-for-any-given-date-and-starting-fiscal-month)
You will need to setup Variables and so, but they are intuitive enough.
Hope it helps someone else.
Code is:
#######################################################################################
##
## Function Name: FN_CreateFiscalWeek
## Author:
## Date: 10 March 2011
## Version: 1.0
## Purpose:
## Usage: FN_CreateFiscalWeek(<Month Number>, <Full Date>, <Day of Week starts on (0 for Monday | 1 for Sunday)>)
## Output: Financial Week Number (Integer)
##
#######################################################################################
## Set initial Vars
$L_myDate = $P_FullDate;
$L_WeekNum = NULL;
$L_Year = year($L_myDate) + 1;
$L_firstWeek = NULL;
$L_StartMonth = $P_MonthNumber ;
## Assume this to be Sunday if incorrectly set in call;
$L_SundayStartofWeek = $P_SundayStartofWeek ;
if ( $L_SundayStartofWeek > 1 )
begin
$L_SundayStartofWeek = 1;
end
## Set up the firstWeek correctly
$L_firstWeek = to_date( '04/' || $L_StartMonth || '/' || $L_Year , 'dd/mm/yyyy');
## Find the beginning of this week
$L_DatePart_W = 1 - day_in_week( $L_firstWeek )- $L_SundayStartofWeek;
$L_firstWeek = $L_firstWeek + $L_DatePart_W ;
while ( $L_myDate < $L_firstWeek ) ##Repeat the above steps but for previous year
begin
$L_Year = $L_Year -1;
$L_firstWeek = to_date( '04/' || $L_StartMonth || '/' || $L_Year , 'dd/mm/yyyy');
$L_DatePart_W = 1 - day_in_week( $L_firstWeek )-1;
$L_firstWeek = $L_firstWeek + $L_DatePart_W ;
end
$L_WeekNum = ($L_Year * 100) + ((date_diff( $L_firstWeek , $L_myDate , 'D') / 7) +1 );
$L_WeekNumChar = substr( to_char( $L_WeekNum),5,2);
$L_WeekNum = cast( $L_WeekNumChar, 'int');
## Generate Output
return $L_WeekNum ;
easte72106 (BOB member since 2006-02-13)
Good grief, all these mental hoops to jump through. Create a Date Dimension and be done with it.
eganjp (BOB member since 2007-09-12)
Tell that to my customer!! This is part of the date dimension, along with another 23 attributes.
easte72106 (BOB member since 2006-02-13)
Just one more idea, does any source system have a calendar or functions for dates? I used that when reading from SAP ERP…
Werner Daehn (BOB member since 2004-12-17)