QlikView - Master Calendar

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 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.