When using dashboards, time is almost always an important factor. Users will always be interested in viewing data by time period, and often times comparing data between two time periods. In a typical data warehouse a time dimension is usually present. If there is a situation where a time dimension is not available, then it is best practice to create a master calendar by taking the date field from the fact table. This master calendar will contain all the combinations of date and time and will join to the fact table.
This blog post details the steps to create a master calendar in Qlik Sense using a date field from a fact table.
We will be using the ORDER_DAY_DT from the fact table below to create our master calendar.
Steps:
1. Create Master Calendar. Make sure to rename the TempDate column to ORDER_DAY_DT, we want the calendar to be able to join back to the fact table. This part of the script is creating all of your typical date dimensions.
2. Generate a Temp table for dates.
3. Generate the rows in the calendar table by using the auto generate function. This function is used to generate rows automatically.
4. The script for the calendar should look like this when finished.
5. After running the above script, your data model should look like this. Note that the two tables are being joined by ORDER_DAY_DT.
Here is a preview of the data inside the Master Calendar.
There are many advanced ways in which the master calendar can be used, such as combining budget and actual data, but I will blog about those another time. This quick walk through should get you started on using this calendar feature.
Free eBook Download
In the eBook “Having a Conversation with Data”, learn what the current BI infrastructure has been and associated challenges with the traditional approach. How important the user experience is in order to best maximize data’s value (think visualizations!!) to your organization and how to gain a competitive advantage with modern analytics platforms.
