Qlik Community

Qlik Sense Enterprise Documents & Videos

Documents & videos about Qlik Sense.

Derived Calendar Fields

thkarner
Contributor III

Derived Calendar Fields

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.

Fiscal year in time-aware chart.png

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!

Attachments
Comments
qlikplaut
New Contributor II

Thank you for sharing your thoughts and findings. Will take it intro consideration in the next implementation, can really make a difference!

Very nice first article BTW.

Best regards,

Bogdan

roblange
New Contributor II

Great post and example app. Thanks for sharing your insights!

Agree with Bogdan, indeed a well written article.

Cheers

Rob.

muratakkemik
New Contributor II

WELL DONE... It was very helpfull.

Version history
Revision #:
1 of 1
Last update:
‎03-31-2017 10:32 AM
Updated by: