Select Page

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.

%d bloggers like this: