In QlikView, many times we need to create a calendar reference object, which can be linked to any data set present in QlikView’s memory. For example, you have a table that captures the sales amount and sales date but does not store the weekday or quarter, which corresponds to that date. In such a scenario, we create a Master Calendar which will supply the additional date fields like Quarter, Day, etc. as required by any data set.
Input Data
Let us consider the following CSV data files, which are used as input for further illustrations.
SalesDate,SalesVolume
3/28/2012,3152
3/30/2012,2458
3/31/2012,4105
4/8/2012,6245
4/10/2012,5816
4/11/2012,3522
Load Script
We load the above input data using the script editor, which is invoked by pressing Control+E. Choose the option Table Files and browse for the Input file.
Next, we load the above data to QlikView’s memory and create a Table Box by using the menu Layout → New Sheet Objects → Table Box where we choose all the available fields to be displayed as shown below.
Create Master Calendar
Next, we create the Master Calendar by writing the following script in the script editor. Here we use the table DailySales as a resident table from which we capture the Maximum and Minimum dates. We load each of the dates within this range using the second load statement above the resident load. Finally, we have a third load statement, which extracts the year, quarter, month, etc. from the SalesDate values.
Select Fields
After the creation of the complete load script along with the master calendar, we create a table box to view the data using the menu Layout → New Sheet Objects → Table Box
Final Data
The final output shows the table showing the Quarter and Month values, which are created using the Sales data and Master Calendar.
