Select Page

If you’re doing a quick prototype from a source system generally you won’t have a nicely formatted date dimension you would typically find in an enterprise data source (I have worked on a Genesis Contact Centre platform that did – hence the generally there). The good news is it’s reasonably straight forward to skin something up, over my next few blogs I am going to talk about the various calendar options within Qlik Sense: Master Calendars, Link Tables, Calendar Islands and Canonical Date Fields.

There are a couple of options for Calendars, and a couple of gotchas too. As a data architect, it’s important to understand the data model and employ the best option for the scenario at hand, so be sure to follow my blog!

Starting with the simplest form, Master Calendars, let’s get into it…

Master Calendars are efficient on models with a small number tables containing one or two date columns. Essentially we analyse the date field to find it’s range or minimum and maximum dates contained. Let’s build a mock data set to demonstrate this:

dates:
Load * Inline [
employee, startdate
jim, 2007-11-01,
jane , 2004-08-05,
peter, 2001-03-17,
geoff, 1997-07-12
];

In our mock data set, we can see employee Geoff has been with the company the longest and Jim is the most recent employee. So what we need to do is calculate all possible dates that fall within this range of two dates. With a larger transactional data sets it’s likely we will have records on each day. So first we need to compute what minimum and maximum values in the range, and we can use the Resident function to reference the inline dates table generated in the prior step to do this:

temp:
Load
MIN(startdate) AS startdate,
MAX(startdate) AS enddate
Resident dates;

We want to place those values into variables for calculation:

Let vStartDate = Num(Peek(‘startdate’,0,dates));
Let vEndDate = Num(Peek(‘enddate’,0,dates));

And then use the number of days between the start and end date to generate a sequential range or list of every date between the start and end date, the Autogenerate function is great for this:

buildrange:
Load
Date($(vStartDate) + RowNo() -1) AS DateRange
Autogenerate
$(vEndDate)- $(vStartDate);

So with a giant list of dates (3764 to be exact), we need to spawn off a date table, with all sorts of useful date variables, like day name, month, quarter year and perhaps something like a weekend flag, once again we use the Resident function to reference our buildrange dataset:

calenderdim:
Load
Date(Date#(DateRange),’YYYYMMDD’) as DateKey,
DateRange AS startdate,
Day(DateRange) AS DayNum,
Week(DateRange) AS WeekNum,
Year(DateRange) AS YearNum,
WeekDay(DateRange) AS DayName,
Month(DateRange) AS MonthName,
Week(DateRange) & ‘-‘ & Year(DateRange) AS WeekYear,
Date(MonthStart(DateRange), ‘MM-YYYY’) AS MonthYear,
‘Q’ & Ceil(Month(DateRange) / 3) AS CalQuarter,
Quarterstart(DateRange, 0, 1) AS QuarterStart,
Quarterend(DateRange, 0 , 1) AS QuarterEnd,
Weekstart(DateRange) AS WeekStart,
Weekend(DateRange) AS WeekEnd,
Date(MonthStart(DateRange)) AS MonthStart,
Date(MonthEnd(DateRange)) AS MonthEnd,
DateRange – Date(MonthStart(DateRange)) AS DayOfMonth,
DayNumberOfQuarter(DateRange) AS DayOfQuarter,
DayNumberOfYear(DateRange) AS DayOfYear,
If(Match(WeekDay(DateRange), ‘Sun’, ‘Sat’) = 0, ‘N’, ‘Y’) AS WeekendFlag
Resident buildrange;

Being a tidy kiwi, we want to drop all the tables we generated along the way which are used to generate the final calendardim table:

Drop Tables temp, buildrange;

Then if you reference the Data Model Viewer you should be presented with a clean data model:

Whilst that’s great for a single date attribute, things get a little more pear shaped when you have multiple dates, let’s go ahead and modify the inline table load to include an end date, and modify things to cater for more than one resulting calendar table:

dates:
Load * Inline [
employee, startdate, enddate
jim, ‘2007-11-01’, ‘2008-11-17’
jane , ‘2004-08-05’, ‘2006-04-01’
peter, ‘2001-03-17’, ‘2006-05-06’
geoff, ‘1997-07-12’, ‘1997-08-10’
];

 

temp:
Load
MIN(startdate) AS startdatestart,
MAX(startdate) AS startdateend,
MIN(enddate) AS enddatestart,
MAX(enddate) AS enddateend
Resident dates;

 

Let vStartStartDate = Num(Peek(‘startdatestart’,0,dates));
Let vStartEndDate = Num(Peek(‘startdateend’,0,dates));
Let vEndStartDate = Num(Peek(‘enddatestart’,0,dates));
Let vEndEndDate = Num(Peek(‘enddateend’,0,dates));

 

startbuildrange:
Load
Date($(vStartStartDate) + RowNo() -1) AS startDateRange
Autogenerate
$(vStartEndDate)- $(vStartStartDate);

 

endbuildrange:
Load
Date($(vEndStartDate) + RowNo() -1) AS endDateRange
Autogenerate
$(vEndEndDate)- $(vEndStartDate);

 

startcalenderdim:
Load
Date(Date#(startDateRange),’YYYYMMDD’) as DateKey,
startDateRange AS startdate,
Day(startDateRange) AS DayNum,
Week(startDateRange) AS WeekNum,
Year(startDateRange) AS YearNum,
WeekDay(startDateRange) AS DayName,
Month(startDateRange) AS MonthName,
Week(startDateRange) & ‘-‘ & Year(startDateRange) AS WeekYear,
Date(MonthStart(startDateRange), ‘MM-YYYY’) AS MonthYear,
‘Q’ & Ceil(Month(startDateRange) / 3) AS CalQuarter,
Quarterstart(startDateRange, 0, 1) AS QuarterStart,
Quarterend(startDateRange, 0 , 1) AS QuarterEnd,
Weekstart(startDateRange) AS WeekStart,
Weekend(startDateRange) AS WeekEnd,
Date(MonthStart(startDateRange)) AS MonthStart,
Date(MonthEnd(startDateRange)) AS MonthEnd,
startDateRange – Date(MonthStart(startDateRange)) AS DayOfMonth,
DayNumberOfQuarter(startDateRange) AS DayOfQuarter,
DayNumberOfYear(startDateRange) AS DayOfYear,
If(Match(WeekDay(startDateRange), ‘Sun’, ‘Sat’) = 0, ‘N’, ‘Y’) AS WeekendFlag
Resident startbuildrange;

 

endcalenderdim:
Load
Date(Date#(endDateRange),’YYYYMMDD’) as DateKey,
endDateRange AS enddate,
Day(endDateRange) AS DayNum,
Week(endDateRange) AS WeekNum,
Year(endDateRange) AS YearNum,
WeekDay(endDateRange) AS DayName,
Month(endDateRange) AS MonthName,
Week(endDateRange) & ‘-‘ & Year(endDateRange) AS WeekYear,
Date(MonthStart(endDateRange), ‘MM-YYYY’) AS MonthYear,
‘Q’ & Ceil(Month(endDateRange) / 3) AS CalQuarter,
Quarterstart(endDateRange, 0, 1) AS QuarterStart,
Quarterend(endDateRange, 0 , 1) AS QuarterEnd,
Weekstart(endDateRange) AS WeekStart,
Weekend(endDateRange) AS WeekEnd,
Date(MonthStart(endDateRange)) AS MonthStart,
Date(MonthEnd(endDateRange)) AS MonthEnd,
endDateRange – Date(MonthStart(endDateRange)) AS DayOfMonth,
DayNumberOfQuarter(endDateRange) AS DayOfQuarter,
DayNumberOfYear(endDateRange) AS DayOfYear,
If(Match(WeekDay(endDateRange), ‘Sun’, ‘Sat’) = 0, ‘N’, ‘Y’) AS WeekendFlag
Resident endbuildrange;

 

Drop Tables temp, startbuildrange, endbuildrange;

So now we have managed to generate two date tables, one for our start date, and one for our end date, along with a fair wack of code, but taking a look at the Data Load Progress, we have some issues; circular references and synthetic keys:

This is further evidenced by the Data Model Views, it ain’t pretty:

But there is more than one way to skin a cat, we can employ the Qualify / Unqualify operators in our calendar table generations (note that it’s best practice to issue an Unqualify after each block or Qlik Sense will continue to apply the Qualify to the following table):

Qualify *;
Unqualify startdate;

 

startcalenderdim:
Load
Date(Date#(startDateRange),’YYYYMMDD’) as DateKey,
startDateRange AS startdate,
Day(startDateRange) AS DayNum,
Week(startDateRange) AS WeekNum,
Year(startDateRange) AS YearNum,
WeekDay(startDateRange) AS DayName,
Month(startDateRange) AS MonthName,
Week(startDateRange) & ‘-‘ & Year(startDateRange) AS WeekYear,
Date(MonthStart(startDateRange), ‘MM-YYYY’) AS MonthYear,
‘Q’ & Ceil(Month(startDateRange) / 3) AS CalQuarter,
Quarterstart(startDateRange, 0, 1) AS QuarterStart,
Quarterend(startDateRange, 0 , 1) AS QuarterEnd,
Weekstart(startDateRange) AS WeekStart,
Weekend(startDateRange) AS WeekEnd,
Date(MonthStart(startDateRange)) AS MonthStart,
Date(MonthEnd(startDateRange)) AS MonthEnd,
startDateRange – Date(MonthStart(startDateRange)) AS DayOfMonth,
DayNumberOfQuarter(startDateRange) AS DayOfQuarter,
DayNumberOfYear(startDateRange) AS DayOfYear,
If(Match(WeekDay(startDateRange), ‘Sun’, ‘Sat’) = 0, ‘N’, ‘Y’) AS WeekendFlag
Resident startbuildrange;

 

Unqualify;

 

Qualify *;
Unqualify enddate;

 

endcalenderdim:
Load
Date(Date#(endDateRange),’YYYYMMDD’) as DateKey,
endDateRange AS enddate,
Day(endDateRange) AS DayNum,
Week(endDateRange) AS WeekNum,
Year(endDateRange) AS YearNum,
WeekDay(endDateRange) AS DayName,
Month(endDateRange) AS MonthName,
Week(endDateRange) & ‘-‘ & Year(endDateRange) AS WeekYear,
Date(MonthStart(endDateRange), ‘MM-YYYY’) AS MonthYear,
‘Q’ & Ceil(Month(endDateRange) / 3) AS CalQuarter,
Quarterstart(endDateRange, 0, 1) AS QuarterStart,
Quarterend(endDateRange, 0 , 1) AS QuarterEnd,
Weekstart(endDateRange) AS WeekStart,
Weekend(endDateRange) AS WeekEnd,
Date(MonthStart(endDateRange)) AS MonthStart,
Date(MonthEnd(endDateRange)) AS MonthEnd,
endDateRange – Date(MonthStart(endDateRange)) AS DayOfMonth,
DayNumberOfQuarter(endDateRange) AS DayOfQuarter,
DayNumberOfYear(endDateRange) AS DayOfYear,
If(Match(WeekDay(endDateRange), ‘Sun’, ‘Sat’) = 0, ‘N’, ‘Y’) AS WeekendFlag
Resident endbuildrange;

 

Unqualify;

So no errors in the Data Load Progress:

And the data model looks pretty good:

However on closer inspection, as we had to Qualify the columns in each calendar our column names are startcalendardim.x and endcalendardimx, not so pretty. This poses some challenges for the visualizations, each of these attribute’s titles needs to be amended (if you would like decent presentation). Renaming attributes can be done at the Chart level, or by creating Master Items. While you can have 2 Master items of the title CalQuarter which reference their respective tables, that means you need to mouseover each item to figure out where it comes from:

On a small data model with few dates (e.g. for a prototype) this might be alright but in the long run, it’s really not sustainable. Another challenge with this method is where the model has two linked fact tables, with at least one or more calendar attributes attache because the date values are not conformed you cannot take measurable attributes from each fact and plot them against each other on a time series.

That’s all for this time, hope this helps your understanding of where Master Calendars fit in!

Thomas – MacGyver of code

Thomas blogs about reporting platforms, data warehousing and the systems behind them.

You can read Thomas’s other blog posts here.

We run regular business intelligence courses in both Wellington and Auckland.

%d bloggers like this: