Master Calendar:
If we want to look data by year, month, week and date wise we go for master calendar. Similarly we may have scenarios where we analyze loads of data for group of years and month.
How to Create a Master calendar:
We create the master calendar in Qlikview by writing the below script:
Step1: Here we use a Date field in Table1.
For Example: Table1: //Table name
Emp_id | Date | E_Hours |
101 | 1/1/2001 | 3 |
102 | 2/1/2002 | 4 |
103 | 4/2/2004 | 6 |
104 | 6/3/2010 | 6 |
Step2: In that RESIDENT table (Table1) we capture the Minimum and Maximum date.
For Example: MinMax:
LOAD Min(Date) as MinDate,
Max(Date) as MaxDate
Resident Table1;
Here we find the Minimum date (1/1/2001) and Maximum date (6/3/2010) from Table1.
Step3: Here we are using Let variable to save the Min and Max date.
For Example: LET vMin = Peek ('MinDate');
LET vMax = Peek ('MaxDate');
DROP Table MinMax;
Step4: It will store the vMin and vMax as Variable.
Step5: Here Peek considers first Row by default.
Step6: We load each of the date within this (Max and Min) range as TempCalendar.
Step7: Next we load fields which extracts the year, quarter, month, week, date from the date field.
For Example:
TempCalendar:
LOAD Date as Date,
Year (Date) as Year,
Month (Date) as Month,
‘Q’ & ceil (month (Date)/3) as Quarter,
Week (Date) as Week,
Day (Date) as Day;
LOAD Date ($(vMin) + IterNo () - 1) as Date
AutoGenerate 1
While $(vMin) + IterNo () - 1 <= $(vMax);
Explanation: In the table TempCalendar we are loading the date Date ($(vMin) (stored in vMin variable) along with IterNo () by checking with the while condition While $(vMin) + IterNo () - 1 <= $(vMax) as Date field.
We using this Date field to create year, month, Quarter, date, week and day.
