Skip to content

Instantly share code, notes, and snippets.

@mikeharding
Last active April 1, 2019 21:37
Show Gist options
  • Save mikeharding/4c7125b153a3160ba8d5 to your computer and use it in GitHub Desktop.
Save mikeharding/4c7125b153a3160ba8d5 to your computer and use it in GitHub Desktop.
Another Qlik Master Calendar example
//****************************************************************************************
// 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