Crafting Qlik Sense Calendars Part 4 of 4 – Canonical Dates

by | Apr 12, 2018


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:

employee:
Load * Inline [
employee, startdate
jim, 2007-11-01,
jane , 2004-08-05,
peter, 2001-03-17,
geoff, 1997-07-12
];
salesorder:
Load * Inline [
saledate, customer, productid, employee, cost, price
2016-06-19, tracy, 1, jim, 1, 5,
2017-12-31, jan, 5, peter, 2,8,
2013-11-05, lars, 4, jane, 4, 12,
2005-08-01, brady, 3, geoff, 5, 9,
2018-02-02, alex, 5, jim, 2, 10
];

Next, we want to create a sub routine which we will call; I’ve included some code to handle financial years as it was a recent requirement:

// vFiscalYearStartMonth change the value to whatever financal year structure your working with
SET vFiscalYearStartMonth = 7;
// expected parameters to the sub routine
SUB CalendarFromField(date, calendarname, alias)
[$(calendarname)]:
// spawn Calendar
LOAD
// standard calendar
[$(date)] ,Day([$(date)]) as [$(alias)DayNum] ,WeekDay([$(date)]) as [$(alias)DayName] ,Week([$(date)]) as [$(alias)Weeknum] ,Month([$(date)]) as [$(alias)MonthName] ,Year([$(date)]) as [$(alias)YearNum] ,Week([$(date)]) & ‘-‘ & Year([$(date)]) as [$(alias)WeekYear] ,Date(MonthStart([$(date)]), ‘MM-YYYY’) AS [$(alias)MonthYear] ,’Q’ & Ceil(Month([$(date)]) / 3) AS [$(alias)CalQuarter] ,Quarterstart([$(date)], 0, 1) AS [$(alias)QuarterStart] ,Quarterend([$(date)], 0 , 1) AS [$(alias)QuarterEnd] ,Weekstart([$(date)]) AS [$(alias)WeekStart] ,Weekend([$(date)]) AS [$(alias)WeekEnd] ,Date(MonthStart([$(date)])) AS [$(alias)MonthStart] ,Date(MonthEnd([$(date)])) AS [$(alias)MonthEnd] ,DayNumberOfQuarter([$(date)]) AS [$(alias)DayOfQuarter] ,DayNumberOfYear([$(date)]) AS [$(alias)DayOfYear] ,If(Match(WeekDay([$(date)]), ‘Sun’, ‘Sat’) = 0, ‘N’, ‘Y’) AS [$(alias)WeekendFlag] // Financial Calendar
,Mod(Month([$(date)]) – $(vFiscalYearStartMonth), 12) +1 as [$(alias)FinMonthNum] ,Month(Date#(Mod(Month([$(date)]) – $(vFiscalYearStartMonth), 12) +1, ‘M’)) as [$(alias)FinMonthName] ,YearName([$(date)], 0, $(vFiscalYearStartMonth)) as [$(alias)FinYearNum] ;
// Generate range of dates between min and max.
LOAD
date(DateMin + IterNo()) as [$(date)] WHILE DateMin + IterNo() <= DateMax
;
// Find min and max of date to generate a range
LOAD
min(datefield)-1 as DateMin
,max(datefield) as DateMax
;
// Load date field values.
LOAD
FieldValue(‘$(date)’, RecNo()) as datefield
AutoGenerate FieldValueCount(‘$(date)’);
END SUB

Then we need to create a link between the employee and tables below the data load script:

DateLink:
Load
employee,
startdate AS Date,
’employee’ AS DateType
Resident employee;
Load
employee,
saledate AS Date,
‘salesorder’ AS DateType
Resident salesorder;

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:

CALL CalendarFromField(‘startdate’, ’employeeCalendar’, ’employee.’);
CALL CalendarFromField(‘saledate’, ‘salesorderCalendar’, ‘salesorder.’);

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

Thomas blogs about reporting platforms, data warehousing and the systems behind them.
Read Thomas’s other blog posts here.

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

1 Comment
  1. JAMI

    Thank you very much Thomas for explaining in detail, very useful. However, when I’m trying to create Calendar table calling the subroutine, debugger finds error “Autogenerate: generate count is out of range: LOAD FieldValue(‘Date’, RecNo()) as datefield AutoGenerate FieldValueCount(‘Date’)” at:
    LOAD
    FieldValue(‘$(date)’, RecNo()) as datefield
    AutoGenerate FieldValueCount(‘$(date)’);
    I’m new to scripting and Qlik, sincerely appreciate your help.
    Thanks & regards,
    Jami

    Reply
Submit a Comment

Your email address will not be published. Required fields are marked *