Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to create a Fiscal Calendar where Fiscal Week runs from Monday - Sunday and starts in where 1st of April falls to.
So for example:
1st April 2015 was Wednesday, therefore 30th March - 5th April is Week 1.
1st of April can be any day from Monday - Sunday and where it falls this will be always Fiscal Week 1. Now, my calendar looks like this:
SET vFiscalYearStartMonth = 4;
Set vCal_FD = 1; // First Day of the week {0=Mon, 1=Tue, ... , 6=Sun}
Set vCal_BW = 0; // Broken Weeks allowed {0=No, 1=Yes}
Set vCal_RD = 1; // Reference day = This day in Jan defines week one {1..7}
LET vStartDate = NUM('04/03/2013');
LET vEndDate =Num('31/03/'& (Right(Today(),4)+1));
MasterCalendar:
LOAD
*
,Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter
,Dual(Month,FiscalMonth)
AS FiscalMonthName;
Load *,
Div( Date - WeekStart( WeekYearRefDate, 0, $(vCal_FD) ) + 7, 7 ) as WeekNumber,
Year( WeekYearRefDate ) as WeekYear;
Load *,
Date( YearStart( If( $(vCal_BW), Date, WeekRefDate )) + $(vCal_RD) - 1) as WeekYearRefDate;
Load *,
Date( WeekStart( Date, 0, $(vCal_FD) ) - $(vCal_RD) ) as WeekRefDate;
LOAD
*
,YeartoDate(Date)*-1 AS CurYTDFlag
,YeartoDate(Date,-1)*-1 AS LastYTDFlag
,inyear(Date, Monthstart($(vEndDate)),-1) AS RC12
,WeekDay(Date) AS WeekDay
,Day(Date) AS Day
,Year(Date)&'-'& week(Date) AS Year_Week
,Div(Date-YearStart(Date,0,4),7)+1 & '-' & WeekYear(Date) AS Week_Year
,Week(Date) AS Week // Standard Calendar Week
,date(monthstart(Date), 'MMM-YYYY') AS MonthYear
,Mod(Month(Date), 13) AS MonthNum // Standard Calendar Month Number
,Month(Date) AS Month // Standard Calendar Month
,Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/3)) AS Quarter // Standard Calendar Quarter
,Year(Date) AS Year // Standard Calendar Year
,Div(Date-YearStart(Date,0,4),7)+1 AS FiscalWeek // Fiscal Week
,Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth // Fiscal Calendar Month
,YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYear
,AutoNumber(YearName(Date, 0, $(vFiscalYearStartMonth))) AS NumFiscalYear; // Fiscal Calendar Year
LOAD
Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date
,RangeSum(Peek('RowNum'), 1) AS RowNum
AutoGenerate vEndDate - vStartDate + 1;
Running this script I get the following:
However, the Fiscal Week 1 for 2015-16 should start on Monday 30th March.
Any suggestions?
I have had a look at the HC blog posts: Fiscal Year and this one Redefining the Week Numbers
