Skip to main content
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT

Derived Calendar Fields

No ratings
Showing results for 
Search instead for 
Did you mean: 
Partner - Creator III
Partner - Creator III

Derived Calendar Fields

Last Update:

Mar 31, 2017 10:32:11 AM

Updated By:


Created date:

Mar 31, 2017 10:32:11 AM



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.


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.


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.




  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




  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


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.




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!

Partner - Contributor III
Partner - Contributor III

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,


Partner - Contributor III
Partner - Contributor III

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

Agree with Bogdan, indeed a well written article.



Contributor III
Contributor III

WELL DONE... It was very helpfull.

Contributor III
Contributor III

 Thank you!  Well written and very valuable.  

Question:  how do you recommend going about using this option when working across multiple tables and various date fields?  The optimization CALL for each date field would create over 10 calendar in my case.

And to forgo the optimization (CALL), still leaves me with a Fall Calendar for each Date Field...  though I can live with this, I wanted to get your input on how you'd go about this.  I don't think the using the derived calendars agains cannonical or index tables would not work due to the large number of data fields.

But again, great work here and I'll continue to use this as my go to method for calendar building!

Creator III
Creator III

But there is no common field for fiscal year, month, quarter for global selection to be used in filter, if user wants to select data of any fiscal year irrespective of table from which date is associated they won't be able to. Pls suggest some them to implement this.

Version history
Last update:
‎2017-03-31 10:32 AM
Updated by: