The Fastest Dynamic Calendar Script (Ever)

[tseebach]

Me and a colleague have put together a new Calendar, combining a super fast method for generating the needed calendar, while also removing a lot configuration options in the previous methods.

So if you're using a  Min/Max based approach like the one described here: Creating A Master Calendar

Then you're properly used to waiting for the Min/Max to find the largest values in your dataset. This can take minutes. What we have here takes less than a second. I've actually not been able to time this, because this calendar script takes less than a 1 seconds even on billions of rows on decent hardware. The only exception to this is if you use a date range starting before christ and spans a thousand years. If you have a date range, with a lot of holes in it (dates with no data), then you should use the last parameter:

Call Calendar('[Date (NK)]','MyCal','Pre.','','true');

The Attached qvw shows both methods (remember the qvs file).

I've not commented it, because most people will not grasp what is going on anyway, and don't need to

To try it out, include the following in your script, and add this to your code:

Call Calendar('DateField');

/* ************************************************************************************

itelligence Standard Qlik Calender Torben Seebach/Martin Didriksen Special Thanks to Rob Wunderlich and John Witherspoon

Configure Calender parameters

  Syntax:

       Calendar(DateField[,CalendarName][,Prefix[,Suffix[,FullCalendar]]])

  Example:

       Call Calendar('Date (NK)','MyCal','Pre.','','true'); // creates a the MyCal table and fields are prefixed with Pre. In the superfast variat

       Call Calendar('Date (NK)'); //creates a table called calendar based of "Date (NK)" field

  Mandatory:

       @_DateField Contains the name of the field to connect the calendar to

  Optional:

       @_CalendarName Contains the name of the calendar we create      
       @_CalendarPrefix A Prefix for all fields
       @_CalendarSuffix A Suffix for all fields      
       @_FullCalendar If this contains a value then it creates a calendar with all dates from Min(Date) to Max(Date), default is the slowest option

************************************************************************************ */

Sub Calendar (_DateField,_CalendarName,_CalendarPrefix,_CalendarSuffix,_FullCalendar)

  Let _StartTime = Now(); 
  Let _CalendarName = If(Len('$(_CalendarName)')=0,'Calendar','$(_CalendarName)');
  Let _CalendarPrefix = If(Len('$(_CalendarPrefix)')=0,'','$(_CalendarPrefix)');
  Let _CalendarSuffix = If(Len('$(_CalendarSuffix)')=0,'','$(_CalendarSuffix)');
  Let _FullCalendar = If(Len('$(_FullCalendar)')=0,1,0);
  Let _DateField = PurgeChar(_DateField,'"[]');

  "$(_CalendarName)":
   LOAD
        Distinct [$(_DateField)] as [$(_DateField)],
        Text(Date([$(_DateField)])) as [$(_CalendarPrefix)DateText$(_CalendarSuffix)],
        Year([$(_DateField)]) as [$(_CalendarPrefix)Year$(_CalendarSuffix)],
        Week([$(_DateField)])&'-'&Year([$(_DateField)]) as [$(_CalendarPrefix)WeekYear$(_CalendarSuffix)],
        Week([$(_DateField)]) as [$(_CalendarPrefix)Week$(_CalendarSuffix)],
        Month([$(_DateField)]) as [$(_CalendarPrefix)Month$(_CalendarSuffix)],
        Year([$(_DateField)])&'-'&Text(Date([$(_DateField)],'MM')) as [$(_CalendarPrefix)YearMonth$(_CalendarSuffix)],         'Q'&Ceil(Month([$(_DateField)])/3) as [$(_CalendarPrefix)Quarter$(_CalendarSuffix)],
        AutoNumber(MonthStart([$(_DateField)]),'_MonthSerial') as [$(_CalendarPrefix)MonthSerial$(_CalendarSuffix)],
        AutoNumber(QuarterStart([$(_DateField)]),'_QuarterSerial') as [$(_CalendarPrefix)QuarterSerial$(_CalendarSuffix)],

        AutoNumber(weekyear([$(_DateField)]) &'|'&week([$(_DateField)]),'_WeekSerial') as [$(_CalendarPrefix)WeekSerial$(_CalendarSuffix)] ;

  If _FullCalendar=1 Then
        LOAD Date(_DateStart+(Iterno()-1),'$(DateFormat)' ) as [$(_DateField)]
               While (_DateStart+(Iterno()-1)<=_DateStop);
        LOAD
               Floor(Min(Fieldvalue('$(_DateField)',RecNo()))) as _DateStart,
               Floor(Max(Fieldvalue('$(_DateField)',RecNo()))) as _DateStop
        AUTOGENERATE FieldValueCount('$(_DateField)');
Else
        LOAD Num(Fieldvalue('$(_DateField)',RecNo())) as [$(_DateField)]
        AUTOGENERATE FieldValueCount('$(_DateField)');
End If

  /* clean variables  */

Let _TotalTime = Round((Now()-_StartTime)*60*60*24,0.00000000001);
Let _StartTime = NULL;
Let _vDateStart = NULL;
Let _vDateStop = NULL;
Trace $(_CalendarName) created in: $(_TotalTime) seconds;
Let _TotalTime = NULL;

ENDSUB

Updated 2014-05-23:

• Implement preceding load method suggested by Rob Wunderlich
• Fixed Timer to show seconds
• Corrected error in documentation
• Added PurgeChar so [Date A],"Date B" also works.
• Thanks to Stephen Redmond for pointing to a boolean error in line 22

Other than my brilliant Collegue Martin Didriksen, I also want to give John Witherspoon some credit for inspiration on the autogerate method used. I think he was the first who posted it online.

[stephencredmond]

Just to clarify, my argument is that it is not always necessary to calculate min and max values from the data.  This is not "hardcoding", just being pragmatic.

In the real world, the min value will rarely change and is usually well known - it is the earliest date in your record set and you can work it out once and not have to do it everyday.

The max date value will most likely be today, yesterday or some derivation of those values - depending on the business.  Again, this is easily and quickly calcuable.

My calendar script will look something like this:

Let vStartDate=Floor(MakeDate(2009,1,1));

Let vEndDate=Floor(Today());

Let vDiff=vEndDate-vStartDate+1;

Calendar:

Load *,

  Date(MonthStart(DateID), 'YYYY-MM') As YearMonth,

  Year & '-' & Quarter As YearQuarter,

  WeekYear & '-' & Num(Week, '00') As YearWeek;

Load

  DateID,

  Year(DateID) As Year,

  Month(DateID) As Month,

  Date(DateID) As Date,

  Day(DateID) As Day,

  Week(DateID) As Week,

  'Q' & Ceil(Month(DateID)/3) As Quarter,

  WeekYear(DateID) As WeekYear,

  -Year2Date(DateID) As YTD_Flag,

  -Year2Date(DateID, -1) As LYTD_Flag;

Load

  RecNo()-1+$(vStartDate) As DateID

AutoGenerate($(vDiff));

This calculates in a sub-second time.

Regards,

Stephen

[stevedark]

Personally, I tend not to use a calendar that hangs off the side of my fact tables at all.  I give my reasons in this blog post:

Why You Should Ditch Your QlikView Calendar

I would welcome the thoughts of others to what I put forward in the post.

- Steve

[tseebach]

Stephen,

Why not put your calendar script into a subroutine and pass the variables dynamically?

For easier reuse in the whole environement?

[#5]

Thank you SO much Torben Seebach, its very useful........

[stephencredmond]

I didn't say that I didn't!

[#7]

Hi,

I like the speed of the script, however when using your example files the week listbox lists week 53. How is this possible? Seems like an error to me.

Also a question: My date field contains yearmonth, for example 201307 and 201401. How do I get the script to recognize it in the right way?

Regards

[tseebach]

Hi Coen,

Depending on when your standard week 53 exists, from the iso week wiki:

An ISO week-numbering year (also called ISO year informally) has 52 or 53 full weeks. That is 364 or 371 days instead of the usual 365 or 366 days. The extra week is referred to here as a leap week, although ISO 8601 does not use this term. Weeks start with Monday. The first week of a year is the week that contains the first Thursday (and, hence, 4 January) of the year. ISO week year numbering therefore slightly deviates from the Gregorian for some days close to 1 January.

Your field needs to be cast as date. 201307 is not a date, so it needs to be converted. That can be done by Date#('DateField'&'01','YYYYMMDD')

[#9]

Ah! I didnt realize that. There we learn something new 🙂 and thanks for the explanation of my datefield.

[stephencredmond]

We also need to be careful when calculating a WeekYear field because the first couple of days of the year could be in week 52 or 53 of the previous year or the last couple of days of the year could be in week 1 of next year.  Luckily, QlikView has a WeekYear function that will give you the correct year to go with the ISO week number.

Regards,

Stephen

[blaise]

Would be good if Qv supported (within the week*() function) different ways of calculation the week - rather strange you cannot change this.