Another Qlik Master Calendar example

MasterCalendar_v2.qvs

//****************************************************************************************
// 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;