Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Introduction
The feature to derive fields was already introduced in Qlik Sense 1.1.
With Qlik Sense 3.x this functionality gets more and more important since the Add Data wizard detects date fields and automatically creates derived calendar fields. Those fields can be easily used to handle different periods of time, e.g. years, quarters, months and weeks. A new wizard in 3.2 is even creating set analysis syntax based on those fields.
Overview
As this functionality is developing more and more there are also some traps when using it in projects.
Therefore I wanted to share an overview of my findings (in my first article):
- Derived (calendar) fields are not stored - they are "virtual fields" calculated at runtime
- In case you want to use the smart "time-aware charts" the derived calendar fields must be used - persistently stored fields in the data model couldn´t be used for it
- In case of large data sets, the base date fields for the derived calendar must be stored in a separate table containing the dates only - otherwise you´re facing serious performance issues
- With a further derived calendar, time-aware charts could even handle visualisations based on fiscal years
Below you´ll find the details for each single finding, including a Qlik Sense app (built in 3.2.2.) demonstrating the behaviour.
Basics
In case the Add Data wizard detects a date field an "calendar" is created automatically. Expanding the field in the edit mode of a sheet shows further subsequent fields (e.g. Year, Quarter, etc.).
A look into the Data load editor and the Auto-generated section reveals the background of the generated fields.
A set of fields is declared in the autoCalendar. Those fields are created when deriving the autoCalendar from a date field.
[autoCalendar]:
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
...
(WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,
Week(Today())-Week($1) AS [WeekRelNo] ;
DERIVE FIELDS FROM FIELDS [Sales Order Date] USING [autoCalendar] ;
Stored or Virtual?
Derived fields are just virtual fields. They are calculated at runtime, not sure if this is done only once or at every use.
I couldn´t find any information in the documentation about this, but all tests I did indicate that derived fiels are virtual.
Furthermore I could follow a personal conversation between a Qlik employee and the Qlik support in Sweden where this was confirmed.
Prerequisites for time-aware charts
The time-aware charts (e.g. line chart) provides a smart visualisation of time-axis including zoom-in/-out. This functionality (called continuous scale) is based on the generated autoCalendar only. I tried different ways to use the continuous scale based on persistently stored fields in the data model (like a manually created master calendar), but there was no way to make it work.
My conclusion is: If you want to use the continuous scale in the time-aware charts you need to use the derived fields.
Large data sets
In case you want to apply a derived calendar on a date field in larger data sets (e.g. 150 million of records) the performance of the system goes significantly down. Loading a sheet took 8-10 minutes on a high-performance server!
In order to solve the issue a single table per date field just containing the date field and a link to the facts table needs to be created. The table holds in this case only a few thousand records with the distinct dates. Applying the derived calendar on such a field brings back the performance as expected!
In my attached demo app running on my laptop with 20 million of records the difference to show the first sheet was
- 4:10 mins with unoptimized data model
- 5 secs with optimized data model (using separated table for the date field)
Please be aware of best-practice technique to create the additional table. A "Load DISTINCT" could take long when running the script.
Instead of this the function FieldValue should be used. Find an example in the function CreateDateTable in the attached app.
Support of Fiscal Years?
Time-aware charts are even working with fiscal years. In order to support this functionality I created a second calendar definition (called Fisc in my example) and used it to derive the calendar fields for the fiscal year.
It´s obviously necessary to have 2 separate definitions of calendar, one for calendar year view, one for fiscal year view. For me it wasn´t possible to combine it into one field definition.
LET vFM = 4; // first month of the fiscal year
[Fisc]:
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
Dual(Right(YearStart($1,0,vFM),2)&'/'&(Right(YearStart($1,0,vFM),2)+1), YearStart($1,0,vFM)) AS [Year] Tagged ('$axis', '$year'),
Dual('Q'&Num(Ceil(Num(Month(AddMonths($1,1-vFM)))/3)),Num(Ceil(NUM(Month(AddMonths($1,1-vFM)))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
Dual(Right(YearStart($1,0,vFM),2)&'/'&(Right(YearStart($1,0,vFM),2)+1)&'-Q'&Num(Ceil(Num(Month(AddMonths($1,1-vFM)))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
Dual('Q'&Num(Ceil(Num(Month(AddMonths($1,0,1-vFM)))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
Dual(Month($1), Month($1)) AS [Month] Tagged ('$month', '$cyclic'),
Dual(Right(YearStart($1,0,vFM),2)&'/'&(Right(YearStart($1,0,vFM),2)+1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
// Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),
Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified')
// If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,
// Year(Today())-Year($1) AS [YearsAgo] ,
// If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,
// 4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,
// Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,
// If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,
// 12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,
// Month(Today())-Month($1) AS [MonthRelNo],
// If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,
// (WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,
// Week(Today())-Week($1) AS [WeekRelNo]
;
// create derived fields for fiscal year
DERIVE FIELDS FROM FIELDS OrdDt USING [Fisc] ;
I only created the definitions for Year, Quarter, Month and Date. Feel free to enhance this for the commented fields.
When using those new fields in the time-aware charts the representation for fiscal years was fully supported. See also sheet "Derived Calendar (Fiscal Year)" in my example.
Doesn´t it look nice?
Your comments are appreciated.
Best,
Thomas
Update
In Patch 1 of June Release there was an update on this.
Auto Calendar to slow with larger data sets
Jira issue ID: QLIK-71145 Description: Performance improvement when using generated date dimensions (auto calendar) for very large datasets.
Your feedback is highly appreciated!
