Sharing the script using the link from HIC's blog
Holiday:
Mapping
LOAD Holidays,
1
FROM
[..\..\Downloads\Dummy data.xlsx]
(ooxml, embedded labels, table is [Holiday List]);
Data:
LOAD [Invoice Date]
FROM
[..\..\Downloads\Dummy data.xlsx]
(ooxml, embedded labels, table is Data);
// ========= Fast method, using symbol tables ===================================================================
MinMaxDate:
Load
Num(YearStart(Min(Fieldvalue('Invoice Date',RecNo())))) as MinDate,
Num(Max(Fieldvalue('Invoice Date',RecNo()))) as MaxDate
Autogenerate FieldValueCount('Invoice Date');
Let vMinDate = Peek('MinDate',0,'MinMaxDate');
Let vMaxDate = Peek('MaxDate',0,'MinMaxDate');
Drop Table MinMaxDate;
// ==============================================================================================================
// === Step 3a: Explanation of the used parameters ===
// ==============================================================================================================
// ==============================================================================================================
// === The First Month Of The Year =============================================================================
// This variable defines on which month the fiscal year starts. It is used only for the month-based fiscal
// calendar.
SET vCal_FM = 1; // First Month of the year {1=Jan, 2=Feb, ... , 12=Dec}
// ==============================================================================================================
// === Step 3b: Generate Week-based 4-4-5 Calendar ===
// ==============================================================================================================
Set vCal_FD = 1; // First Day of the week {0=Mon, 1=Tue, ... , 6=Sun}
Set vCal_RD = 5; // Reference day = The day that always belong to week one
// --- Calculate Start date -------------------------------------------------------------------------------------
Let vWeekStart = WeekStart(vMinDate-vCal_FD)+vCal_FD;
Let vRefDate = YearStart(vWeekStart-vCal_RD+7)+vCal_RD-1;
Let vStartDate = WeekStart(vRefDate-vCal_FD)+vCal_FD-1;
// --- Calculate End date ---------------------------------------------------------------------------------------
Let vWeekStart = WeekStart(vMaxDate-vCal_FD)+vCal_FD;
Let vRefDate = AddYears(YearStart(vWeekStart-vCal_RD+7)+vCal_RD-1,1);
Let vEndDate = WeekStart(vRefDate-vCal_FD)+vCal_FD-1;
// --- Generate a help table with all weeks of one year ---------------------------------------------------------
WeeksOfOneYear445:
Load
If(MonthOfQuarter=1 and WeekOfMonth=1,(RecNo()-1)*7,Peek(QuarterOffset))
as QuarterOffset,
If(WeekOfMonth=1,(RecNo()-1)*7,Peek(MonthOffset))
as MonthOffset,
Num(RecNo(),'00') as Week,
*;
Load // --------------------------------------- Generate all Weeks -------
Num(RecNo(),'00') as Month,
IterNo() as WeekOfMonth,
*
While IterNo() <= NoOfWeeksInMonth or (Quarter=4 and MonthOfQuarter=3 and IterNo()<=6);
Load // --------------------------------------- Generate 12 Months -------
RecNo() as Quarter,
IterNo() as MonthOfQuarter,
SubField('4-4-5','-',IterNo()) as NoOfWeeksInMonth
Autogenerate 4
While IterNo() <= 3;
// --- Create mapping tables based on the help table ------------------------------------------------------------
Get445Quarter:
Mapping Load Week, Quarter Resident WeeksOfOneYear445;
Get445QuarterOffset:
Mapping Load Week, QuarterOffset Resident WeeksOfOneYear445;
Get445Month:
Mapping Load Week, Month Resident WeeksOfOneYear445;
Get445MonthOffset:
Mapping Load Week, MonthOffset Resident WeeksOfOneYear445;
Drop Table WeeksOfOneYear445;
// --- The Calendar ---------------------------------------------------------------------------------------------
WeekBased445:
LOAD *,
If(MonthStart = Previous(MonthStart),
If(HolidayFlag = 0, RangeSum(Peek('Nth Day by Month_CY'), 1), Peek('Nth Day by Month_CY')), 1) as [Nth Day by Month_CY],
If(QuarterStart = Previous(QuarterStart),
If(HolidayFlag = 0, RangeSum(Peek('Nth Day by Qtr_CY'), 1), Peek('Nth Day by Qtr_CY')), 1) as [Nth Day by Qtr_CY],
If(YearStart = Previous(YearStart),
If(HolidayFlag = 0, RangeSum(Peek('Nth Day by Yr_CY'), 1), Peek('Nth Day by Yr_CY')), 1) as [Nth Day by Yr_CY];
Load // --------------------------------------- Grain = Year -------------
Year as Year,
YearStart as YearStart,
// --------------------------------------- Grain = Quarter ----------
Dual('FQ' & Quarter,Quarter) as Quarter,
QuarterStart as QuarterStart,
Dual(Year & ' FQ' & Quarter,QuarterStart) as YearQuarter,
// --------------------------------------- Grain = Month ------------
Dual('FP' & Month,Month) as Month,
MonthStart as MonthStart,
Dual(Year & ' FP' & Month,MonthStart) as YearMonth,
Dual('FP' & MonthOfQuarter,MonthOfQuarter) as MonthOfQuarter,
// --------------------------------------- Grain = Week -------------
Dual('FW' & Week,Week) as Week,
WeekStart as WeekStart,
Dual(Year & ' FW' & Week,WeekStart) as YearWeek,
Dual('FW' & WeekOfMonth,WeekOfMonth) as WeekOfMonth,
// --------------------------------------- Grain = Day --------------
DayOfYear as DayOfYear,
DayOfQuarter as DayOfQuarter,
DayOfMonth as DayOfMonth,
WeekDay as WeekDay,
Date as [Invoice Date],
If(ApplyMap('Holiday', Date, Null()) or Match(WeekDay, 'Sat', 'Sun'), 1, 0) as HolidayFlag;
Load // ------------------------------------------------------------------
Mod(Month-1,3)+1 as MonthOfQuarter,
Div(Date-MonthStart,7)+1 as WeekOfMonth,
Date-QuarterStart+1 as DayOfQuarter,
Date-MonthStart+1 as DayOfMonth,
*;
Load // ------------------------------------------------------------------
ApplyMap('Get445Quarter',Week,'ERROR') as Quarter,
ApplyMap('Get445Month',Week,'ERROR') as Month,
Date(YearStart+ApplyMap('Get445QuarterOffset',Week,'ERROR'))
as QuarterStart,
Date(YearStart+ApplyMap('Get445MonthOffset',Week,'ERROR'))
as MonthStart,
*;
Load // ------------------------------------------------------------------
Num(Div(Date-YearStart,7)+1,'00') as Week,
Date-YearStart+1 as DayOfYear,
*;
Load // ------------------------------------------------------------------
Year(RefDate) as Year,
Dual(WeekDay(Date),Date-WeekStart) as WeekDay,
Date(WeekStart(RefDate-$(vCal_FD))+$(vCal_FD))
as YearStart,
*;
Load // ------------------------------------------------------------------
Date(YearStart(WeekStart-$(vCal_RD)+7)+$(vCal_RD)-1)
as RefDate,
*;
Load // ------------------------------------------------------------------
Date(WeekStart(Date-$(vCal_FD))+$(vCal_FD)) as WeekStart,
*;
Load // ------------------------------------------------------------------
Date($(vStartDate)+RecNo()) as Date
Autogenerate vEndDate - vStartDate ;
