TLDR: A new Qlik Sense training video uses a tired old-school Master Calendar script. I propose that updated training materials should use an updated script.
I just watched a new video by Michael Tarallo of Qlik titled “Understanding the Master Calendar – Qlik Sense and QlikView“. Before I pick on the contents of this particular video, I want to credit Michael for producing many excellent training videos that are worth watching and learning from. I highly recommend them.
The video does a great job of explaining the need for and function of a Master Calendar in your data model. It then goes on to show an actual script.
I can’t discuss Master Calendar without expressing disappointment that Calendar generation is not yet a builtin function in Sense. Something like QlikView Components (QVC) does with the single script line:
CALL Qvc.CalendarFromField('OrderDate');
On to the script used in this new video. I’ll reproduce the entire script below and then comment on the techniques used and suggest some more “modern” approaches.
The video script is similar to the script used in the current QlikView Developer course . I acknowledge that this script works as is and produces correct results. But I don’t think it should be taught to newbies as good scripting. Here’s the script from the video:
Obsolete Code
1. Why is this field created and where is it used?
It’s not used. It’s left over from a very old version of the exercise and it doesn’t serve any purpose.
2. Why are we sorting the table? Is this statement useful?
Even if I could think of a good reason why the Calendar table should be in order, it already is in this order because the TempCalendar was generated in a loop. Statement unnecessary.
Inefficient Code
Loading a Resident table can be very slow for a large table.
Experienced scripters use the FieldValues array instead.
What’s the difference? FieldValues only reads the distinct values of a field — maybe a thousand or so for several years of dates. Resident reads every row of the table. For a 10M row fact table, that’s 10M reads and the time scales up linearly. The difference can be dramatic.
Error Prone and Extra Work
Peek(), used on lines 13 & 14, is one of those functions that fails silently. That is, if you misspell a field or table, you won’t get a script error. Misspelling a variable will also not generate a script error. Maybe. Or maybe not. Or maybe you will get a Calendar that starts in year 1899. Depends on which line you make the spelling error on. If your misspelling does result in a script syntax error, it will be downstream from where you created the problem. There are multiple ways to mess this one up and some very curious potential results.
Don’t forget to DROP those temp tables on lines 15 and 35.
And those varMinDate and varMaxDate variables really should be cleared as well.
You can avoid all the syntax traps and extra cleanup by coding this a as a Preceding Load. Here’s the same script written as a Preceding Load:
Nothing to remember (or forget) to clean up. If you misspell a fieldname, you will get an understandable error at the right place. This is the calendar script I wish we would provide to newcomers.
Of course if you’ve attended the Masters Summit for QlikView, you’ve learned all about FieldValues, Preceding Loads and more. If not, attend a summit this Fall in NYC or Copenhagen.
QVC users don’t even get out of bed to generate Calendars. But they know that all that best practice stuff is happening under the covers. If you want to learn more about QVC, join my online class June 4 or a future class at Q-on.bi.
-Rob
Entire script suitable for copying:
MasterCalendar:
Load
TempDate AS OrderDate,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
;
//=== Generate a temp table of dates ===
LOAD
date(mindate + IterNo()) AS TempDate
,maxdate // Used in InYearToDate() above, but not kept
WHILE mindate + IterNo() <= maxdate;
//=== Get min/max dates from Field ===/
LOAD
min(FieldValue('OrderDate', recno()))-1 as mindate,
max(FieldValue('OrderDate', recno())) as maxdate
AUTOGENERATE FieldValueCount('OrderDate');
Beautiful work as always Rob! Fortunately I’ve seen pieces of this at the Masters Summit for QlikView and have integrated some of these already.
Question – when loading from FieldValue, is it quicker to do both min and max in the same LOAD statement, or to break into two LOADs?
I like the single statement as you’ve done it here to take advantage of the preceding load. But I’m just curious.
I would think it’s quicker to do it in a single LOAD. Either way, the difference would probably be a matter of milliseconds so I would go for code clarity.
Hi Rob,
Nice tip. I wonder why we even need to bother with extracting min and max dates. Min date can be stored permanently in a variable and max date can be today’s date.
Then, I’d simply use left keep to reduce the calendar to the date range fact table has.
Also, Qlikview goes its marry way even if you misspell mapping table name so we can create quarters without using a mapping table.
Hi Shilpan,
Yes, there are many other approaches to creating calendars. I was limiting myself to discussing the particular use case and script used in the video.
Since Masters Summit for Qlikview – London 2013- (where It was presented to me) I use QVC calendar and I do not use other scripts because it’s fast, clean and it’s easy to use!
And yes, QV should integrate some solutions from QVC.
Good job Rob, and thank you for QVC !
Nice post Rob.
Standard calendar is covered with this Master Calendar, but we all know life is not that simple as we have to utilize fiscal/financial calendars with different start&end dates and different periods within those years(e.g. 4-4-5 and all possible combinations). The only thing I would like to have available in QV/Sense are standard functions developed to create both calendars. I simply use one Excel file I have to refresh once in a year. Not a big problem, but still 🙂
Related to this topic is the global variable QT developed in Sense to set a starting day of a week (Sun instead of Mon, for example) and this still does not exist in QV.
Cheers
Great post as always Rob!
After a recent conversation I had with Henric C, I’d comfortably say one statement is almost always better than 2. Each time a statement is executed the internal DB is unpacked, the statement is run, and then the DB is closed/packed. This overhead of unpacking and packing can be significant if a large amount of data has already been loaded. And as you know, proceeding loads have a number of other speed and resource benefits over resident loads.
As an example of how drastic the packing overhead can be, I recently had a customer with a very large app in which they were dropping a series of temp tables at the end of the script (not best practice obviously). The dozen or so individual drop statements were taking several minutes to run. Combining them into a single drop tables statement reduced that to a few seconds. The main difference being the single unpack and pack rather than a dozen.
Regards
Matt
Hey Rob, thanks for the mention and kind words. First off, you know in my eyes (and I am sure in everyone else’s eyes) you are a Qlik rockstar! So, being mentioned and in your blog is an honor and privilege. In regards to the calendar script, yes you are correct, I got this from some of our basic training materials that cover this topic – which uses a simpler format and as you pointed out may not be as efficient. As you know the concept was to really have those understand the importance of the master calendar and implement it quickly. However, I have made a suggestions to our teams to take a look at this post and possibly take what is used here into consideration. To add some context, dates and calendar work are always being considered for enhancements and improvements to the products.
And .. now if it is OK, I will post a shameless plug:
You can check out plenty more Qlik Sense related videos and get started quickly on the Qlik Community.
https://community.qlik.com/docs/DOC-6932
Every Thursday at 2 PM EST I present Getting Started with Qlik Sense to those who are just getting started, so please join me if you can: http://go.qlik.com/Qlik_Sense_Webinar.html?sourceID1=QV_EV_Site
Thanks again Rob, hope to see you at a future event so we can share a beer.
Regards,
Mike Tarallo
Qlik
Follow me on twitter @mtarallo
Dear Rob,
Thank you for your valuable insights. We sometimes do not take the time to look into existing code.
I am looking into your script and for some reason I do not understand, lines 19, 20 and 21 do not work.
Even the following does not seem to give what I expect:
LOAD
IterNo() as TempDate
While 1+IterNo() < 7;
It returns nothing at all.
Any advice?
Regards,
Dominique
Your load statement, by itself as shown, would return nothing because there is no input source. Tray adding an “AutoGenerate 1” to the statement.
In my code example, the input is provided by the following load that generates the mindate & maxdate fields.
I had shifted some code to the top so I could execute step by step and there I missed.
Thank you Rob, it is working marvellously now.
I am getting this error, what am I missing?
Script line error:
LOAD
min(FieldValue(‘%DateKey’, recno()))-1 as mindate,
max(FieldValue(‘%DateKey’, recno())) as maxdate
AutoGenerate FieldValueCount(%DateKey)
FieldValueCount(%DateKey) should have quotes.
FieldValueCount(‘%DateKey’)
Excellent script. Having one problem applying this to a date field because :
FIELDVALUE(‘Quotation_CreatedDate’,RECNO()) AS test
gives me a long list of question marks.
Have you seen this before and do you have a cure?
What causes this symptom? I have never ever seen this before.
I have seen the “????” values when the data is in a different codepage than QV expects during the LOAD. Do the ??? values show up in a listbox?
Yes, I get one questionmark for each row autogenerated.
Interesting is that if I use this date field with Qlikview Components and ‘CalendarFromField’ I do get a correct and working calendar.
It looks like in this thread but I can’t solve it like they did:
https://community.qlik.com/thread/32173
Will you do this course on QVC again?
I meant do it “again”
The next QVC course is scheduled for July 1. See q-on.bi for details.
Is there a copy of the new and improved calendar script? If so May I download it and see if I like it better than the old one?
I am about to start on doing a dashboard … of previously done single documents and I am getting a headache already 🙂
Jo
LET varToday = Num(today());
QuartersMap:
MAPPING LOAD
rowno() as Month,
‘Q’ & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Set vFM = 3 ; // First month of fiscal year
MasterCalendar:
Load Dual(fYear-1 &’/’& fYear, fYear) as FYear, // Dual fiscal year
Dual(Month, fMonth) as FMonth, // Dual fiscal month
*;
Load Year + If(Month>=$(vFM), 1, 0) as fYear, // Numeric fiscal year
Mod(Month-$(vFM), 12)+1 as fMonth, // Numeric fiscal month
*;
Load
TempDate AS OrderDate,
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,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
inyeartodate(TempDate, $(varToday), 0, 10) * -1 AS CurYTDFlag,
inyeartodate(TempDate, $(varToday), -1, 10) * -1 AS LastYTDFlag,
WeekDay(TempDate) as WeekDay
;
LOAD
date(minDate + IterNo()) as TempDate
While minDate + IterNo() <= maxDate
;
LOAD
min(FieldValue('OrderDate',recno()))-1 as minDate,
max(FieldValue('OrderDate',recno())) as maxDate
AUTOGENERATE FieldValueCount('OrderDate');
Here you go Jo 🙂
Should work, I tested it…
oops
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
replace this one by
inyear(TempDate, Monthstart($(maxDate)),-1) as RC12,
Hello, I’m a newbie to Qlikview and programming 🙁 I have loaded the fields I need and right after I included the script “MasterCalendar” from Rob, and for whatever reason I’m losing all years except for the first, I replaced the field OrderDate” with the field I need the system to used as OrderDate, everything seems to be working as there are no errors, but I can’t graph the years, months, weeks, quarter, etc….. 🙁
Any help will be GREATLY appreciated !!!!
Thanks,
Carlos R.
Hello again 🙂 I just found the error – cueck – I was using the wrong field for the “OrderDate” – everything is working just find.
Now, can anyone help me to set the Fiscal Calendar (Year, Quarters, Months) starting on April??????
THANKS !
Carlos R.
If you are looking to create Fiscal calendars, you might want to take a look at the calendar available in http://qlikviewcomponents.org
Hi Rob, thanks for your prompt reply, I downloaded the Master File, but I’m not quite sure how can I use the Fiscal Calendar script within my code and how to take advantage of other scripts included in the Master file.
I’ll run some test myself to see if I can find the way I should use this scripts!
Thanks !!!!
Hi Carlos,
Once you download the latest qvc-nn.zip release file from
https://github.com/RobWunderlich/Qlikview-Components/releases
unzip the file, and open up the Doc\QvcDocumentation.qvw file. Look at the “Using QVC” sheet for setup, and the Details sheet for how to use the Qvc.CalendarFromField() routine with a fiscal year. If you have more questions ask on the QVC Forum at https://groups.google.com/forum/#!forum/qlikview-components-users.
I teach an online class on using QVC. Next class is Jan 6.
http://q-on.bi/courses/qlikview-components-scripting-library/
Hi Rob, thanks so much for your help, I’ll proceed as indicated and will check your online classes to evaluate my participation.
THANK YOU AGAIN !!!! You have being very helpful !
Carlos
Hi Rob! Love your blog and book (still reading and learning)! Is there a way to limit the min year on this script? I’ve tried putting a where clause into the master calendar, but I suspect it needs to limited in the preceding load.
min(FieldValue (‘Opportunity Close Date’, recno()))-1 as mindate,
I don’t know why you couldn’t put a where clause in the top load — the MasterCalendar — like:
WHERE Year(TempDate) > 2007
I tested this and it worked for me. If you have a problem, can you post the WHERE clause you are using?
Sorry for the late reply. I suppose I checked the incorrect box to notify me of replies.
I placed the WHERE clause in the top and it worked. I guess I just assumed incorrectly on the location of there WHERE clause in the script.
Thanks for the help!
Dear Rob Sir,
Simply I’m a great fan of you. I’ve some queries regarding this topic.
1) I was trying Autogenerate in place of IterNo() with While loop at line no 19, 20, 21. Here is my code block-
LOAD
MinDate + RowNo() -1 as TxDate
AutoGenerate MaxDate – MinDate +1;
This code is throwing a reload error. I want to know isn’t Autogenerate allowed when doing preceding load ?
2) Can the code block at line no 24, 25, 26, 27 be replaced by the following code-
LOAD
Max(TranDate) as MaxDate,
Min(TranDate) as MinDate
Resident FACT_TABLE;
I want to know why you used FieldValue() function with Autogenerate bcoz this code block giving proper output ? I’ll wait for your reply. Thanks in advance.
Regards,
Joy
Oh I’m sorry. I got the answer of the second question from this post only. But still I have some doubts. Please go through the following code block.
FACT_TABLE:
LOAD * Inline
[
TranDate
03/04/2012
16/02/2016
];
CALENDAR_MASTER:
LOAD
Min(YearStart(FieldValue(‘TranDate’,RecNo()),0,4)) as MinDate,
Max(FieldValue(‘TranDate’,RecNo())) as MaxDate
AutoGenerate FieldValueCount(‘TranDate’);
Here I want to create sequential date from 01/04/2012 to 16/02/2016. But when I’m applying your way in the CALENDAR_MASTER its not giving proper output.
MinDate is giving 01/04/2012 which is desired output. But in case of MaxDate I’m getting a ‘?’ mark. Please guide me.
Regards,
Joy
? Is showing because fieldvalue assigns an unknown format by default. However, the internal value should be correct. You can see the value by putting MaxDate in a list box and overriding the format, or adding a date() format function in the script.
Thank you sir. Its working fine now. But my first problem isn’t answered yet and I’m still in doubt. Here it is-
Case 1:
CALENDAR_MASTER:
LOAD
MinDate + RowNo() -1 as TxDate
AutoGenerate MaxDate – MinDate +1;
LOAD
Min(YearStart(FieldValue(‘TranDate’,RecNo()),0,4)) as MinDate,
Max(Date(FieldValue(‘TranDate’,RecNo()))) as MaxDate
AutoGenerate FieldValueCount(‘TranDate’);
This code block is throwing an error showing “Script Line error”.
Case 2:
Applying your method-
CALENDAR_MASTER:
LOAD
(MinDate + IterNo() – 1) as TrxDate
While MinDate + IterNo() <= MaxDate + 1;
LOAD
Min(YearStart(FieldValue('TranDate',RecNo()),0,4)) as MinDate,
Max(Date(FieldValue('TranDate',RecNo()))) as MaxDate
AutoGenerate FieldValueCount('TranDate');
This is working fine as you said. I'm following Case 2 only but not understanding why Case 1 is not working.
Thanks in advance.
Regards,
Joy
Your case 1 is an attempt at a preceding load. However, the Autogenerate keyword in the top load makes it not a preceding load.
So now you have a load like this:
CALENDAR_MASTER:
LOAD
MinDate + RowNo() -1 as TxDate
AutoGenerate MaxDate – MinDate +1;
Which will fail because there is no source for the fields MaxDate and MinDate.
Its absolutely clear now sir. Thanks for helping. I already build a Calendar_Master. And its working real good.
Dear Rob,
I would like to share one of my views regarding this post. In your script you using the following code to generate Quarter.
‘Q’ & ceil(month(TempDate) / 3) AS Quarter,
Its absolutely fine where Financial year starts with January.
But its need customization is the financial year doesn’t starts in January rather starts in mid of the Year (e.g. Apr).
So I was thinking about one mapping load like the following-
1) For financial year starting in Apr-
QUARTER_MAPPING:
Mapping LOAD * Inline
[
Month_Name, Quarter
Apr, Q1
May, Q1
Jun, Q1
Jul, Q2
Aug, Q2
Sep, Q2
Oct, Q3
Nov, Q3
Dec, Q3
Jan, Q4
Feb, Q4
Mar, Q4
];
And using the following code in generating the Quarter-
ApplyMap(‘QUARTER_MAPPING’,Text(Month(TrxDate)),’NA’) as Quarter,
It will not be a pain even if the financial year starts in Jan.
Only a little change is needed in the Mapping Table like the following-
QUARTER_MAPPING:
Mapping LOAD * Inline
[
Month_Name, Quarter
Jan, Q1
Feb, Q1
Mar, Q1
Apr, Q2
May, Q2
Jun, Q2
Jul, Q3
Aug, Q3
Sep, Q3
Oct, Q4
Nov, Q4
Dec, Q4
];
This is my little simple idea about the post. Would love to hear from you.
Regards,
Joy
Hi Rob,
Excellent article. I had the chance to use this new approach to create a more efficient master calendars in some of my applications. So, really thanks for sharing this with the community.
Nevertheless, I’d like to say that Calendar generation is already a builtin function in Qlik Sense since 1.1 for those who didn’t know. When you go into the Fields tab in the Assets panel, you will see a tab for Date & Time fields. These fields are generated through the script using the “Declare” and “Derive” functions. You can find more information about this here:
https://community.qlik.com/blogs/qlikviewdesignblog/2015/02/27/qlik-sense-date-time
https://help.qlik.com/en-us/sense/2.0/Subsystems/Hub/Content/Assets/date-time-fields.htm
Hope this helps!
Alvaro P.
max date is assigned null with the script:
max(FieldValue(‘EventDate’,recno())) AS maxDate
thank you very much for this one!
Hi Rob
Thanks for the really nice improvements to the calendar script to make it even more efficient.
One small thing I’ve come across is the line:
Week(weekstart(TempDate)) & ‘-‘ & WeekYear(TempDate) as WeekYear,
Which, if you have a week that crosses the boundary from one year to the next (i.e. January 1st is midweek as it was in 2013), the WeekYear value is represented in the current year, e.g.:
OrderDate = ’02/01/2013′
WeekYear = ’53-2013’
Which isn’t correct, so I’ve made the following amendment to give the correct WeekYear value:
Week(weekstart(TempDate)) & ‘-‘ & WeekYear(WeekStart(TempDate)) as WeekYear,
OrderDate = ’02/01/2013′
WeekYear = ’53-2012’
Possibly not a field that gets used too often, but hope that helps and thanks again for the great script!
Chris
Thanks for that improvement!
Greetings!
Thanks for sharing this very clever version of the calendar generation script.
I am wondering if you could comment on why do the max() and min() of the bottom load (lines 24-27 of your script) actually work on the autogenerated table, since, according to the script manual, the max() / min() function “returns the maximum / minimum numeric value of expression encountered over a number of records as defined by a group by clause.” , but there is no group by clause in the load and FieldValue() returns just one value (so there is no set of values over which to find the min() or max()).
So, that part of the script is working like this one:
LOAD max(maxDate) as maxDate,
min(minDate) as minDate;
LOAD
FieldValue(‘OrderDate’, recno()) -1 as minDate,
FieldValue(‘OrderDate’, recno()) as maxDate
AUTOGENERATE FieldValueCount(‘OrderDate’);
which doesn’t use the group by either, but by the time min()/max() is called, maxDate and minDate already have a set of values. I can’t understand why these two scripts produce equivalent results.
Any thoughts?
Cheers,
–José
When all fields in the load are created by aggregation functions, the result is a single row table. “group by” is unnecessary. Think of it as an “implicit group by”.
(Apologies for the delay in approving your comment. I’ve been off on holiday)
Hi – I’m pretty new to QlikSense but I think I followed this. I have multiple fact tables say (very simply) Orders, Shipments and Invoices and each has an appropriate date OrderDate, ShipDate, InvDate.
Do I need to repeat the first section of the script for each fact table?
E.G.
TempDate AS OrderDate,
…
TempDate AS ShipDate
…
TempDate AS InvDate
…
and then
min(FieldValue(‘OrderDate’, recno()))-1 as mindate,
max(FieldValue(‘OrderDate’, recno())) as maxdate
AUTOGENERATE FieldValueCount(‘OrderDate’);
min(FieldValue(‘ShipDate’, recno()))-1 as mindate,
max(FieldValue(‘ShipDate’, recno())) as maxdate
AUTOGENERATE FieldValueCount(‘ShipDate’);
etc…?
Hi Denis,
Including all 3 date fields in the Calendar will likely produce an undesirable circular reference. For another approachm take a look at this tutorial which is available in both Sense and View versions.
Qlikview Cookbook: Tutorial – Using Common Date Dimensions http://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/
Hi Rob,
tanks for all your work.
I need to have a master calendar that is linked to two date fields in two different fact tables. I watched Mike Tarallo’s video, then landed to your “Using Common Date Dimensions” tutorial but the
CALL CalendarFromField(‘Date’, ‘CommonCalendar’, ‘ ‘)
instuction causes an error on my Qlik Sensedesktop installation.
Then I landed here and tryed to replace that command with this “Generate master calendar” script, but again this master calendar is linked to only one table.
How can I create a master calendar linked to two fact tables?
Hi Rob,
I brought it to work.
I made a mistake in “Load TempDate AS …..,
I loaded it as the field of one of my fact tables instead of loading it as the DATE in my Link table.
Thanks again
Hi Rob,
Thanks for this. Maybe it’s because I read the tutorial on an iPad but not all the tabs work. I’ll have a go on my desktop tomorrow and hope I can get it to work. Many thanks for your help.
Denis
Hi Rob,
On my desktop, I got your tutorial working and I think I understand it. I applied it to my script and I also used your Master Calendar updated from Mike Tarallo’s video.
I loaded data and thought it had all worked but then when I checked the load script everything after the first set up of Date had failed (no colour coding- all left in green). I’d be happy to send you my load script as I’m sure you’d spot my errors very quickly but I don’t want to impose on you without asking permission. Would you be happy to take a look?
Many thanks
Denis
Hi Denis,
I suggest you post this question and your load script over on the community.qlik.com forums. More people there and easier to upload samples. Also take a look in the script execution log found in
C:\Users\userid\Documents\Qlik\Sense\Log
My month start in April. My Fiscal calender is working fine. Now I want a expression or field in script which can give current fiscal month. If today date is 11 Dec 2017 then it should give 9 as result. I want show fiscal month in bar chart with null values. But it shows all the month. I just want april to Dec as current month is December. So i was thinking of using set analysis fMonth should be less than current fiscal month.
Hi ,
Thanks for the knowledge.
However, when I use the fieldvalue function without -1 in calculating min date, I am not getting the YTD flag in against my data.
Can anyone explain me the importance of that -1 .
Thanks
Hi Rob,
I got this failure:
Autogenerate: generate count is out of range
Der Fehler ist hier aufgetreten:
LOAD
min(FieldValue(‘[PRODUKTIONSDATUM]’, recno()))-1 as mindate,
max(FieldValue(‘[PRODUKTIONSDATUM]’, recno())) as maxdate
AUTOGENERATE FieldValueCount(‘[PRODUKTIONSDATUM]’)
What should I do?
Thank you
Your field name should not include brackets.
Thnak you!!!
one more question,
if I want to create a Table (Master Calebder TAble) to join ist with other tables, how Should I do?
Thnak you!!!
To join the calendar to another table , add Join (yourtable) to the topmost Load.
Hi Bob
This master calendar script it fantastic but what is we have to consider multiple dates from fact ?
In your case, there is only order date is considered and its min and max get calculated to define the range of the calendar.
For handling multiple fact dates, see https://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/