Better Calendar Scripts

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


Entire script suitable for copying:

 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 === 
 date(mindate + IterNo()) AS TempDate
 ,maxdate // Used in InYearToDate() above, but not kept 
WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/
 min(FieldValue('OrderDate', recno()))-1 as mindate,
 max(FieldValue('OrderDate', recno())) as maxdate
AUTOGENERATE FieldValueCount('OrderDate');

46 thoughts on “Better Calendar Scripts”

  1. 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.

  2. 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.

  3. 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.

    1. 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.

  4. 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 !

  5. 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.


  6. 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.


  7. 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.

    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:

    Thanks again Rob, hope to see you at a future event so we can share a beer.

    Mike Tarallo
    Follow me on twitter @mtarallo

  8. 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:

    IterNo() as TempDate
    While 1+IterNo() < 7;

    It returns nothing at all.
    Any advice?



  9. 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.

    1. 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.

  10. I am getting this error, what am I missing?

    Script line error:
    min(FieldValue(‘%DateKey’, recno()))-1 as mindate,
    max(FieldValue(‘%DateKey’, recno())) as maxdate
    AutoGenerate FieldValueCount(%DateKey)

  11. 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.

    1. 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?

      1. 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:

  12. 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 🙂

  13. LET varToday = Num(today());
    rowno() as Month,
    ‘Q’ & Ceil (rowno()/3) as Quarter

    Set vFM = 3 ; // First month of fiscal year
    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
    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
    date(minDate + IterNo()) as TempDate
    While minDate + IterNo() <= maxDate
    min(FieldValue('OrderDate',recno()))-1 as minDate,
    max(FieldValue('OrderDate',recno())) as maxDate
    AUTOGENERATE FieldValueCount('OrderDate');

  14. oops

    inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

    replace this one by
    inyear(TempDate, Monthstart($(maxDate)),-1) as RC12,

  15. 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 !!!!


    Carlos R.

  16. 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.

      1. 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 !!!!

  17. Hi Carlos,
    Once you download the latest release file from
    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!forum/qlikview-components-users.

    I teach an online class on using QVC. Next class is Jan 6.

  18. 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 !


  19. 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,

    1. 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?

      1. 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!

  20. 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-
    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-
    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.


    1. 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.
      LOAD * Inline

      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.


      1. ? 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.

        1. 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:
          MinDate + RowNo() -1 as TxDate
          AutoGenerate MaxDate – MinDate +1;

          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-
          (MinDate + IterNo() – 1) as TrxDate
          While MinDate + IterNo() <= MaxDate + 1;

          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.


  21. 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:

    MinDate + RowNo() -1 as TxDate
    AutoGenerate MaxDate – MinDate +1;

    Which will fail because there is no source for the fields MaxDate and MinDate.

    1. Its absolutely clear now sir. Thanks for helping. I already build a Calendar_Master. And its working real good.

  22. 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-
    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-
    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.


  23. 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:

    Hope this helps!

    Alvaro P.

  24. 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!


  25. 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;
    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?



    1. 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)

Leave a Reply

Your email address will not be published. Required fields are marked *