Defining a Master Calendar in Qlik Sense is a common requirement and can be done using the Time
and Date
functions. With Sense, Qlik has introduced the Declare
and Derive
functions, which make it easier to create the Calendar
definition. This is still not commonly used, as most Qlik Sense developers stick to their old Calendar scripts, and there is nothing wrong with that. However, these functions are worth exploring.
Load the following part of the script that gives information on organization sales into the Qlik Sense application:
OrgSales: LOAD Product, OrderNo ,DATE(Date#( InvoiceDate,'DD/MM/YYYY')) as InvoiceDate, Sales INLINE [ InvoiceDate,Product,OrderNo,Sales 1/1/2013,Chains,101,5500 8/2/2014,Seats,101,4800 3/3/2014,Brake Oil,102,6500 9/5/2015,Helmets,104,4500 ];
Using the INLINE
table specified in the preceding code, we will generate a Master Calendar. We will generate the fields and Group
definition using the Declare
function.
Calendar: Declare Field Definition Tagged '$date' Parameters first_month_of_year=1 Fields Year($1) as Year Tagged '$year', Month($1) as Month Tagged '$month', Date($1) as Date Tagged '$date', Week($1,first_month_of_year) as Week Tagged '$week' Groups Year,Month,Date type collection as YearMonthDate;
Calendar
definition is created, it needs to be linked back to the date field using the Derive
function. Insert the following statement in the script and reload the application:Derive Fields from Fields InvoiceDate using Calendar;
Declare
statement.
The Declare
function is used to create the Calendar
definition and tag it to $date
. The Calendar
definition is then used to derive related dimensions such as Year, Month, Week, and so on.
The parameter first_month_of_year
indicates what the first month of the year should be. It contains comma-separated values, but it is optional and can be skipped if needed.
Next, we define the fields we want to generate in the Calendar
table. The $1
represents the data field from which the date field will be generated, which is InvoiceDate
in our case.
When the field definition is used, a comma-separated list of fields is generated. The Derive
function is used in order to generate the derived fields such as Year, Month, and so on from the InvoiceDate
field. The groups are defined at the end of the script that creates a drilldown group for Year, Month, and Date.
The Derive
function can be used to link back the Calendar
to multiple dates separated by a comma. For example, "derive fields from fields InvoiceDate
, ShippingDate
using Calendar
".
Similar to the resident load, a Calendar
table can be loaded again in the script. We can change the parameter value of the first month of the year to 3
. The earlier value of the parameter is overridden by doing this. This is achieved with the following commands:
MyCalendar: DECLARE FIELD DEFINITION USING Calendar WITH first_month_of_year=3; DERIVE FIELDS FROM FIELDS InvoiceDate USING MyCalendar;