Crafting Qlik Sense Calendars Part 4 of 4 – Canonical Dates

In the last 3 parts we have discussed various different approaches to date attributes in Qlik Sense, we have looked at Master Calendars, Link Tables and most recently Date Islands. We have looked at some scenarios where these should be employed, and given equal consideration to scenarios they shouldn’t.
This time around we are going to have a look at canonical dates, which are probably the most complex, but give a great deal of flexibility, especially when implemented in conjunction with Master Calendars, which I will explain in further detail.
So to kick things off we need to create some fictitious data:

Which once run should appear:
And finally, if we want to build out the calendar we need to call the subroutine:

CALL CalendarFromField(‘Date’, ‘Calendar’, ”);

If you’re having issues with the code not running it’s likely your code blocks are in the wrong order with the sub routine being after the data load, it should be structured as follows:
Once all that’s in there and run through the result will look something like this:
What’s great about this is that we now have a common or conforming date dimension. So we could plot values from both employee and salesorder on the same linear date based axis.
Having a common date field is excellent for charts but not so much for selection based activity. So the way to really take it to the next level is to implement master calendars in conjunction with a main date field, and the great news is with the sub routine we simply through it some more parameters and away we go. For instance:

Should net us the following data model:
Note that the conformed calendar attributes are all clearly named, and the salesorder and employee calendars have been qualified, so it’s very clear what belongs to what. By qualifying, we also avoid circular references and synthetic keys in the model.

So, that wraps up this short series on calendar approaches in Qlik Sense, if you’ve been reading the whole way through you would see that there isn’t one single approach to handling dates in Qlik Sense, rather a number of approaches depending on the use case.
For some further reading (and where I drew inspiration for this blog) check out the following threads: Using Common Date Dimensions and Shared Calendars and Canonical Date.Thomas – MacGyver of code