In any Dashboard,Time is an important factor. Users will always be interested in viewing the data by time period and also will be interested in performing comparitive analysis between 2 time periods.
In a data warehouse, a Time dimension table is usually present, which joins to the fact table and produces results.In some situations, time dimension is not present but a date field is present in the fact table.In such scenarios, it is a best practice to create Master calendar by taking the date field in the fact table. This Master table will contain all the combinations of date and time and will join to the fact table.
In this blog i will discuss how to create a Master calendar based on date field in the fact table.
In my previous blogs, i have loaded SalesOrderHeader table. This table contains OrderDate field.
You can always create Time values by applying date time functions on the OrderDate column
Year(OrderDate) As Order_Year
Month(OrderDate) As Order_Month
This will give you Year and Month values but it will give you only the values which are present in the database. If say, for example, there were no Orders in March then this method will not show the March month in the list box and date will not be continous.
To resolve this problem, we will create Master Calendar.
1. Create or set the value of Min date based of the OrderDate in the SalesOrderTable.
This can be done either by hardcoding the start date or by taking the first value of the OrderDate by using Peek()
Peek() will give the value of the OrderDate in the first record
Let varMinDate = Num(Peek(‘OrderDate’,0,’OrderHeader’));
2. Create or set the value of Max date
Peek() will give the value of the OrderDate in the last record
Let varMaxDate = Num(Peek(‘OrderDate’,-1,’OrderHeader’));
3. Generate the rows in the calendar table by using Autogenerate. Autogenerate in QlikView is used to generate rows automatically.
date($(varMinDate) + rowno() – 1) As TempDate
$(varMaxDate) – $(varMinDate) + 1;
4. Create Master Calendar
TempDate As OrderDate,
Week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month