If you’ve ever struggled with constructing a master calendar for a QlikView data model, this post should help reduce your stress levels.
If you’re unsure of what the purpose of a master calendar is, a master calendar helps reduce the redundancy of date information in a data set. Imagine placing in a table a year’s worth of sales transactions for every store in the nation of a “big-box” retailer. The amount of redundant information (i.e. months, days, day names, etc…) would be staggering. What makes better sense is to have a single piece of information denoting the day, such as “200161010” for “October 10, 2016”, and deriving from that in a dimensional table all of the other variations of date information. For each day we can derive things like:
- Year
- Quarter
- Month Number
- Month Name (abbreviated: Jan, Feb, Mar, …)
- Month Name (spelled out: January, February, March, …)
- Week Number (in month)
- Week Number (in year)
- Day (in month)
- Day (in year)
- Weekday (abbreviated: Sat, Sun, Mon, …)
- Weekday (spelled out: Saturday, Sunday, Monday, …)
Having each of these elements stored only once for each day would result in a great saving of space in the data model.
Below is a script that will generate a dimensional table of just such information. The field [Period] in the [Facts] table is the key field that links the fact table to the [Calendar] dimensional table. Let’s look at the script and then break down the steps so that we fully understand what is taking place.
════════════════════════════════════════════
[DateExtents]:
Load
Floor(Num(YearStart(Min([Period]))),1) as [MinDate],
Floor(Num(YearEnd(Max([Period]))),1) as [MaxDate]
Resident [Facts];
Let varMinDate = Peek(‘MinDate’, 0, ‘DateExtents’);
Let varMaxDate = Peek(‘MaxDate’, 0, ‘DateExtents’);
DROP Table [DateExtents];
[AllDates]:
Load
Date($(varMinDate) + IterNo() – 1) as [TempPeriod]
AutoGenerate (1)
While $(varMinDate) + IterNo() – 1 <= $(varMaxDate);
MasterCalendar:
Load
[TempPeriod] as [Period],
Week([TempPeriod]) as [Week],
Year([TempPeriod]) as [Year],
Month([TempPeriod]) as [Month],
Day([TempPeriod]) as [Day],
YeartoDate([TempPeriod])*-1 as [CurYTDFlag],
YeartoDate([TempPeriod],-1)*-1 as [LastYTDFlag],
InYear([TempPeriod], Monthstart($(varMaxDate)),-1) as [RC12],
Date(MonthStart([TempPeriod]), ‘MMM-YYYY’) as [MonthYear],
‘Q’ & Ceil (Month([TempPeriod])/3) as [Quarter],
Week(weekstart([TempPeriod])) & ‘-‘ & WeekYear([TempPeriod])as [WeekYear],
Week(weekstart([TempPeriod])) as [WeekNumber],
WeekDay([TempPeriod]) as [WeekDay],
Date([TempPeriod],’WWWW’) as [WeekName]
Resident [AllDates];
Drop Table [AllDates];
════════════════════════════════════════════
Now, let’s break this all down into plain English.
Step 1
[DateExtents]:
Load
Floor(Num(YearStart(Min([Period]))),1) as [MinDate],
Floor(Num(YearEnd(Max([Period]))),1) as [MaxDate]
Resident [Facts];
This step determines the lowest and highest days in the data. Store the first day of the lowest month in a field named [MinDate] and the last day of the highest month in a field named [MaxDate]. The resulting table name [DateExtents] contains two columns and one row of information for use in Step 2. The dates are being read from a table named [Facts] that was loaded into memory from a load instruction executed earlier in the script, hence the use of the “Resident” command. [Period] is the key field linking the two tables together.
- “Max” and “Min” find the lowest and highest period.
- “YearStart” and “YearEnd” find the first day of the lowest month and the last day of the highest month.
- “Num” converts the dates to numbers.
- “Floor” rounds the numbers down to the closes whole number, thereby dropping any fractional portion, or “time” portion, of the number.
Step 2
Let varMinDate = Peek(‘MinDate’, 0, ‘DateExtents’);
Let varMaxDate = Peek(‘MaxDate’, 0, ‘DateExtents’);
DROP Table [DateExtents];
We now extract the date information from the table created in Step 1 and store the dates as variables for use in Step 3.
The “Peek” function allows us to look inside of a table and see what is located at a particular record position. In this case, we are looking in the [MinDate] and [MaxDate] fields at record position “0” (we count rows using zero-based numbering, where the first row in the table is row zero.) The table we are “peeking” into is [DateExtents] created in the previous step.
Once the earliest and latest dates have been captured as variables for the next step, we no longer require the [DateExtents] table for anything further; it is therefore dropped from the data model.
Step 3
[AllDates]:
Load
Date($(varMinDate) + IterNo() – 1) as [TempPeriod]
AutoGenerate (1)
While $(varMinDate) + IterNo() – 1 <= $(varMaxDate);
It is now time to construct all of the days between the first day of the first month of transactions to the last day of the last month of transactions.
We start constructing days starting with the day stored in “varMinDate”. We want to add the iteration number to the lowest day, except the actual lowest day, so we add the the current iteration number (less one) to the lowest day.
We will use the AutoGenerate function to add 1 to the iteration number and loop back to perform the process again. We will continue to do this as long as the day we are constructing is less than or equal to day stored in the “varMaxDate”variable (the last day of the last month in the transactions.) The results of this activity will construct a table named [AllDates] and contain a single field named [TempPeriod]. This field contains all of the days derived from the looping process.
Step 4
[MasterCalendar]:
Load
[TempPeriod] as [Period],
Week([TempPeriod]) as [Week],
Year([TempPeriod]) as [Year],
Month([TempPeriod]) as [Month],
Day([TempPeriod]) as [Day],
YearToDate([TempPeriod])*-1 as [CurYTDFlag],
YearToDate([TempPeriod],-1)*-1 as [LastYTDFlag],
InYear([TempPeriod], MonthStart($(varMaxDate)),-1) as [RC12],
Date(MonthStart([TempPeriod]), ‘MMM-YYYY’) as [MonthYear],
‘Q’ & Ceil (Month([TempPeriod])/3) as [Quarter],
Week(WeekStart([TempPeriod])) & ‘-‘ & WeekYear([TempPeriod]) as [WeekYear],
Week(WeekStart([TempPeriod])) as [WeekNumber],
WeekDay([TempPeriod]) as [WeekDay],
Date([TempPeriod],’WWWW’) as [WeekName]
Resident [AllDates];
This block of script will create all of the descriptive variants for each day stored in the [AllDates] table. The results are stored in a new table named [MasterCalendar]. There are a myriad of different day/month/year elements that can be constructed from the original date; some of the most common are listed above. You can feel free to omit any of these derived entries that are not applicable to your specific scenario, or add additional field constructions. Make of this what works best for your needs.
Note: The [AllDates] table was created in the previous step (Step 3) and is therefore loaded from memory via the “Resident” command.
Below are several date functions used in this construction process:
- Week
- Year
- Month
- Day
- YearToDate
- InYear
- Date
- Week
- MonthStart
- WeekDay
- WeekStart
- WeekYear
Step 5
Drop Table [AllDates];
Once the master calendar has been constructed, we no longer require the services of the [AllDates] table; it is therefore dropped from the data model.
Additional Notes
There are definitely better, and faster ways of creating master calendar tables. If you are feeling adventurous and wish to dabble with Preceding Load statements, you can accomplish all of the above in a more efficient and less time consuming manner. For those of us who do not consider ourselves “Masters of QlikView”, this less efficient yet perfectly acceptable technique will serve you well.