Crafting Qlik Sense Calendars Part 3 of 4 – Date Islands

by | Apr 10, 2018


In the last two parts of this series, we discussed master calendars and link tables as plausible options for handling dates, situations where they could be effective, along with possible issues.
In much the same manner this time we are going to have a look at date islands, which are a form of a data island.
So let’s get cracking and spin up a will data set:

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
];

Now we have a nice little model which shows sales orders and the relationship between employees, along with the saledate and the employees startdate:

If we open up a sheet and add both dates we can see that they are operating independently:

The issue we are seeing here is that the dates attribute does not conform, in a data warehousing environment you would typically have a date dimension, as in a single table which unifies date attributes.
So with a calendar island, we look to build a single calendar table:
First we define a range, which could be a min or max from one of your tables, generally I like to specify something wild to account for most plausible dates:

Temp:
LOAD * Inline
[
minDate, maxDate
‘1900-01-01′,’2099-12-31’
];

Then put those values into parameters:

LET varMinDate = Num(Peek(‘minDate’, 0, ‘Temp’));
LET varMaxDate = Num(Peek(‘maxDate’, 0, ‘Temp’));
DROP Table Temp;

Work out the date range that we are building:

TempCalendar:
LOAD
$(varMinDate) + IterNo()-1 as Num,
Date($(varMinDate) + IterNo() – 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

And finally construct the calendar:

Calendar:
LOAD
TempDate AS date,
Day(TempDate) AS DayNum,
Week(TempDate) AS WeekNum,
Year(TempDate) AS YearNum,
WeekDay(TempDate) AS DayName,
Month(TempDate) AS MonthName,
Week(TempDate) & ‘-‘ & Year(TempDate) AS WeekYear,
Date(MonthStart(TempDate), ‘MM-YYYY’) AS MonthYear,
‘Q’ & Ceil(Month(TempDate) / 3) AS CalQuarter,
Quarterstart(TempDate, 0, 1) AS QuarterStart,
Quarterend(TempDate, 0 , 1) AS QuarterEnd,
Weekstart(TempDate) AS WeekStart,
Weekend(TempDate) AS WeekEnd,
Date(MonthStart(TempDate)) AS MonthStart,
Date(MonthEnd(TempDate)) AS MonthEnd,
TempDate – Date(MonthStart(TempDate)) AS DayOfMonth,
DayNumberOfQuarter(TempDate) AS DayOfQuarter,
DayNumberOfYear(TempDate) AS DayOfYear,
If(Match(WeekDay(TempDate), ‘Sun’, ‘Sat’) = 0, ‘N’, ‘Y’) AS WeekendFlag
Resident TempCalendar
Order By TempDate ASC;
DROP Table TempCalendar

As a result in the Data Model Viewer we end up with a calendar island which is independent of the other tables because there are no identical column names in the other tables:

Now that we have seen an island sitting on it own the though is probably crossing your mind “how is that going to work?” Well, the “stitching together” happens in the App development layer.
So what we want to do is create a bar chart and then plot Yearnum as the dimension, and we will use an element from each table with differing times as our measure:

count(if(startdate=date,employee))
sum(if(startdate=date,cost))

Now the chart should be plotting multiple metrics (I tidied the appearance of this chart slightly, so yours might not be identical) :

The one limitation worth discussing with this approach is that the code that’s driving this functionality is being calculated at execution time, with large volumes of data, and large date ranges it’s likely there would be performance degradation. So great method for a reasonably small data set, which with well-designed Apps and data processes is definitely possible.
So that’s all for this time, see you next time when we go through canonical dates.
Thomas – MacGyver of code
Thomas blogs about reporting platforms, data warehousing and the systems behind them.

Read Thomas’s other blog posts, including master calendars and link tables here.

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

1 Comment
  1. Devi Saikumar

    Hi Thomas, I am Devi,working as a MI Analyst in an Insurance company. Currently I am working on a report in qliksense where I have one table with Closed Date and Open Date and I want to plot a single chart with number of claims open and closed on last 12 month(YearMonth format Dec2017-Nov2018) against a general date field as dimension. I have tried using your script but its throwing error saying “Field ‘a’ not found” . Could you please help me out to solve this scenario.
    Many thanks in advance
    Devi

    Reply
Submit a Comment

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