In this video I will introduce you to a concept known as the Master Calendar which can be used with both QlikView and Qlik Sense. A Master Calendar is simply a table that contains a time period and time attributes that you define, linked to your existing data.
If you are unable to analyze data by different time dimensions using your existing data set, or have noticed time gaps when create charts and visualizations - the Master Calendar will solve these problems.
Attached are samples below for your reference.
.qvf for Qlik Sense Desktop - copy to C:\Users\<user profile>\Documents\Qlik\Sense\Apps
.qvw for QlikView - open with QlikView Desktop
master.txt - the script used to create the Master Calendar
Please be aware that the Master Calendar can be created a few different ways using Qlik scripting. It can also be made more efficient using specific methods and functions. The example in this post is used as a simple sample.
NOTE:To continue your learning experience, make sure to visit our Education web page at www.qlik.com/training
Hello Antonio - thanks for your question - can you please post this in the main section of the Community forum, that way we can get an extra set of eyes on it?
Michael, great video and explanation. I've applied it to a problem, but in doing so I think I may have come across an issue with the concept; I'd like to hear your take on it.
I've built a *.qvf that draws a fair amount of data from an Oracle 11g data base (2.4 Mrows) as a basis for a set of visualizations in Qlik Sense 3.2.1 desktop.
My first graph was a line trend; it showed typical problem that a master calendar will address (dates that are omitted because the measure has no value for the day).
I followed your example exactly. When I tried to load the data I received an Oracle error "The data base is open for read-only access only". Commenting out the Master calendar entirely (and the command "Proc_Dates:" - equivalent to "Orders:" in the main script) allowed the load to proceed normally.
I'd inserted the "Proc_Dates:" above the "LIB CONNECT TO...." statement, which seemed appropriate.
Is the issue that the script to create the Master Calendar is somehow issuing an update/insert command to the data base? If so, there's nothing to be done about it; the DB is a data mart that has been specifically designed as a read-only data source.
I won't be able to share the file due to sensitive data, so I've attached the scripts; the changes I made to your Calendar script and my load script are in RED.
The Calendar Script:
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(Activityentrydate) as minDate,
max(Activityentrydate) as maxDate
Resident Proc_Dates;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
TempDate AS Activityentrydate,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
My Load Script:
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET CreateSearchIndexOnReload=1;
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
Proc_Dates:
LIB CONNECT TO 'SoD PROD Data Warehouse';
SQL select DP.SR
,DP.Userfullname
,TT.Transaction_Type
,TT.Doc_Type
,DP.GPC
,DP.Gpc_Description
,To_char(DP.Activityentrydate,'HH24')||'00-'||To_char(to_number(to_char(DP.Activityentrydate,'HH24'))+1)||'00' As Work_Window
