Last active
April 1, 2019 21:37
-
-
Save mikeharding/4c7125b153a3160ba8d5 to your computer and use it in GitHub Desktop.
Another Qlik Master Calendar example
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
//**************************************************************************************** | |
// This script builds a master calendar table with both fiscal and calendar based fields | |
//**************************************************************************************** | |
// This is the number of months that the fiscal periods are offset against the calendar | |
// periods. A value of 4 results in a April to March fiscal year. | |
SET vFiscalOffset = 4; | |
// Assign values directly just for SIB | |
//---------------------------------------------------------------------------------------- | |
LET vMinDate = Num(MakeDate(2012, 4, 30)); | |
LET vMaxDate = Num(MakeDate(2014, 12, 31)); | |
LET vCurrPeriod = Num(MakeDate(2014, 8, 31)); | |
LET vCurrFiscalYr = 2014; | |
//Wwe are assuming that the last completed financial period was Feb 2014 | |
LET vPriorPeriod = Num(AddMonths(Date($(vCurrPeriod)), -1)); | |
LET vCurrMonthID = (year($(vCurrPeriod))*12) + month($(vCurrPeriod)); | |
//------------------------ Temporary Calendar -------------------------------------------- | |
// We now generate a table containing all the dates between our MinDate and MaxDate values | |
//---------------------------------------------------------------------------------------- | |
TempCal: | |
LOAD | |
date($(vMinDate) + rowno() - 1) as TempDate | |
AUTOGENERATE | |
$(vMaxDate) - $(vMinDate) + 1; | |
//------------------------ Apply Map ----------------------------------------------------- | |
// This section contains maps to create fields we can't create with functions | |
//---------------------------------------------------------------------------------------- | |
MonthLongMap: | |
Mapping LOAD * Inline [ | |
CalMonth, MonthLong | |
1, January | |
2, February | |
3, March | |
4, April | |
5, May | |
6, June | |
7, July | |
8, August | |
9, September | |
10, October | |
11, November | |
12, December | |
]; | |
//------------------------ Master Calendar ----------------------------------------------- | |
// Using the list of dates created in TempCal we can now create the master calendar | |
// We name our unique date based key OrderDate so that it links back to our data model | |
//---------------------------------------------------------------------------------------- | |
MasterCalendar: | |
LOAD | |
TempDate AS CalDate, | |
Weekday(TempDate) AS WeekDay, | |
Date(MonthStart(TempDate), 'MMMYY') as MonthYear, | |
// calendar year fields | |
Week(TempDate) as CalWeek, | |
Month(TempDate) as CalMonth, | |
Year(TempDate) as CalYear, | |
Day(TempDate) as CalDay, | |
Week(TempDate)&'-'&Year(TempDate) as CalWeekYear, | |
// fiscal year fields | |
num(Month(addmonths(TempDate, 13 - $(vFiscalOffset)))) as FiscalPeriod, | |
Dual(Month(TempDate), num(month(monthstart(TempDate, 13 - $(vFiscalOffset))))) as FiscalMonth, | |
Dual(ApplyMap('MonthLongMap', Month(TempDate), Null()), num(month(monthstart(TempDate, 13 - $(vFiscalOffset))))) as FiscalMonthLong, | |
num(year(addmonths(TempDate, 13 - $(vFiscalOffset)))) as FiscalYear, | |
'Q' & ceil(month(monthstart(TempDate, 13 - $(vFiscalOffset))) / 3) as FiscalQtr, | |
num(year(addmonths(TempDate, 13 - $(vFiscalOffset)))) + ' ' + 'Q' & ceil(month(monthstart(TempDate, 13 - $(vFiscalOffset))) / 3) as FiscalYearQtr, | |
// fields use for determining last month in quarter and last month in year. If year is in progress, last completed period is flagged | |
if(TempDate < $(vPriorPeriod), if(mod(num(Month(addmonths(TempDate, 13 - $(vFiscalOffset)))), 3) = 0, 1, 0), if(TempDate = $(vPriorPeriod), 1, 0)) as QLMFlag, | |
if(TempDate < $(vPriorPeriod), if(Num(Month(addmonths(TempDate, 13 - $(vFiscalOffset)))) = 12, 1, 0), if(TempDate = $(vPriorPeriod), 1, 0)) as YLMFlag, | |
// The following flags are useful for Set Analysis formulas, to get specific date based sub-sets of data | |
// Assume current month is the last COMPLETE month (which is actually prior mth) | |
if(monthstart(TempDate)=monthstart(AddMonths($(vCurrPeriod), -1)), 1, 0) as CurMTDFlag, | |
if(monthstart(TempDate)=monthstart(AddMonths($(vCurrPeriod), -2)), 1, 0) as PriorMTDFlag, | |
// Work out current fiscal year flag and fiscal years Old flag | |
if(year(monthstart(TempDate, 13 - $(vFiscalOffset)))=year(monthstart($(vPriorPeriod), 13 - $(vFiscalOffset))), 1, 0) as CurFiscalFlag, | |
if(year(monthstart(TempDate, 13 - $(vFiscalOffset)))<=year(monthstart($(vPriorPeriod), 13 - $(vFiscalOffset))), | |
year(monthstart($(vPriorPeriod), 13 - $(vFiscalOffset)))-year(monthstart(TempDate, 13 - $(vFiscalOffset))) | |
) as FiscalYearsOld, | |
//Assume current week is the last COMPLETE week (which is actually prior wk) | |
if(WeekStart(TempDate)=WeekStart($(vToday)-7), 1, 0) AS CurrWeekFlag, | |
if(WeekStart(TempDate)=WeekStart($(vToday)-14), 1, 0) AS PriorWeekFlag, | |
// Rolling 12 Months current and prior year | |
if(TempDate>=monthstart(AddMonths($(vCurrPeriod), -12)) AND TempDate<monthstart($(vCurrPeriod)), 1, 0) as Rolling12MonthFlag, | |
if(TempDate>=monthstart(AddMonths($(vCurrPeriod), -24)) AND TempDate<monthstart(AddMonths($(vCurrPeriod), -12)), 1, 0) as RollingPrior12MonthFlag, | |
// Create an index with Current Period being 0 and prior periods numbered according to how many months ago from current period | |
if(num(TempDate)>$(vPriorPeriod), 0, | |
$(vCurrMonthID) - ((year(TempDate)*12) + month(TempDate)) | |
) as PeriodsOld, | |
(year(TempDate)*12) + month(TempDate) - $(vCurrMonthID) as CurYearMthIndex | |
// Load from temporary calendar table | |
RESIDENT TempCal | |
ORDER BY TempDate ASC; | |
//---------------------------------------------------------------------------------------- | |
// Drop the temporary tables | |
//---------------------------------------------------------------------------------------- | |
DROP TABLE TempCal; | |
//---------------------------------------------------------------------------------------- | |
// Drop Day level fields if you only need a Monhly calendar. Reload distinct values to | |
// reduce the rows of data | |
//---------------------------------------------------------------------------------------- | |
DROP Fields CalDate, WeekDay, CalWeek, CalWeekYear, CalDay, CurrWeekFlag, PriorWeekFlag; | |
NoConcatenate | |
TempCalendar: | |
LOAD Distinct * | |
Resident MasterCalendar; | |
DROP Table MasterCalendar; | |
RENAME Table TempCalendar to MasterCalendar; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment