Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!

Understanding the Master Calendar (video)

100% helpful (1/1)
cancel
Showing results for 
Search instead for 
Did you mean: 
Michael_Tarallo
Employee
Employee

Understanding the Master Calendar (video)

Last Update:

May 22, 2015 7:53:13 AM

Updated By:

Michael_Tarallo

Created date:

May 22, 2015 7:53:13 AM

Attachments

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


For details on our latest:


  • Training
  • Certifications
  • Skills assessments

Regards,

Michael Tarallo

Qlik

Comments
Michael_Tarallo
Employee
Employee

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?

Not applicable

I did it. Thanks Michael.
Master calendar granularity

regards

Antonio

0 Likes
fginfrida
Partner - Contributor III
Partner - Contributor III

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

      ,DP.Priority

      ,DP.Activityentrydate

      ,DP.Status

From icis_report.documents_processed DP

Join ICIS.Transaction T

  On T.Service_Request_Number = DP.SR

Join ICIS.Transaction_Type TT

  On TT.Transaction_Typeid = T.Transaction_Typeid;

0 Likes
TZ-Lucas
Partner - Contributor II
Partner - Contributor II

😁very nice,thanks 

Abhishek_Pednekar
Contributor II
Contributor II

Thank you so much.. Its really helpful.. 🙂

 

0 Likes
JonathanTeo
Partner - Contributor II
Partner - Contributor II

I am unable to view the linked video. Youtube says the video is private.

0 Likes
roninags
Contributor II
Contributor II

the video is no longer available though 😞

0 Likes
mklunark
Contributor
Contributor

I'm with some problems when i try to create the master calendar on qlik sense

Unexpected token: ',', expected one of: 'OPERATOR_PLUS', 'OPERATOR_MINUS', 'OPERATOR_MULTIPLICATION', 'OPERATOR_DIVISION', 'OPERATOR_STRING_CONCAT', 'like', 'and', ...
O erro ocorreu aqui:
TempCalendar:
LOAD
40111,333333333 + Iterno()-1 As Num,
Date(40111,333333333 + IterNo() - 1) as TempDate
AutoGenerate 1 While 40111>>>>>>,<<<<<<333333333 + IterNo() -1 <= 44713
0 Likes
Version history
Last update:
‎2015-05-22 07:53 AM
Updated by: