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.
- In the data load editor, type in the following script:
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; - Once the
Calendardefinition is created, it needs to be linked back to the date field using theDerivefunction. Insert the following statement in the script and reload the application:Derive Fields from Fields InvoiceDate using Calendar;
-
On a new sheet, click on edit and then on the Fields tab
on the Assets panel to the left. At the bottom of the panel you will see there is a new tab for the time and date functions. Once you expand this, you should be able to see all the fields we created under the Declarestatement.
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;