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.
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'); // 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.
Well, If you want to benchmark, I think at real test needs to be done. This calendar is fast, because it finds the highest and lowest value and generates any date in between, within the application.
Generating the calendar outside of Qlik, is a very typical datawarehousing method, which is mostly discarded by the Qlik community because of lack of flexibility and speed.
Also the engine and core is written in a different language, parts of this is done in assembly. So its actually much faster than Visual C++, which is only used for the GUI and API.
I think, though, that quite a lot of people would like to grasp what's going on in your script - I would want to know, anyway, before I run a script, and generally speaking, when I don't understand a script, I can't expect my colleagues to do so and I don't use it - I only use code people can understand, even if it's not the fastest possible, and my scripts usually contain quite a nr. of lines of comments.
In my experience, to most people that matters more than waiting a few seconds more or less for a script to complete.
We have had a mastercalendar script, running (on AUTOGENERATE, independent of the data in the specific app, fast enough) from a few years back until the end of next year) and we have included that everywhere ever since, only adding a routine for holidays and for that year-end-week 52/ 53.
In our industry, having a longer-running calendar would not make sense because all the base_files we have for any data (and even the database) change every now and then ...
>> Luckily, QlikView has a WeekYear function that will give you the correct year to go with the ISO week number.
In Qlik Sense 2.0.1 (and so I suppose this may apply to QlikView as well), I have discovered a strange result in date analysis when I extract year and week for certain dates. I created a Seed Calendar table containing one column, a date field called TempDate. I populated it from 1/1/2010 to the present. Among the composite date fields with which I am experimenting are some week-year fields.
I set up these values to rule my date functions:
SET FirstWeekDay=6; SET BrokenWeeks=1;
The following is the minimum of the master calendar code to show the anomaly:
//Create the Master Calendar MasterCalendar: LOAD * ,Week & '-' & If(Week=1 AND Month=12,Year+1, If(Match(Week,52,53) AND Month=1, Year-1, Year)) as WeekYear1;
LOAD TempDate as Date Week(WeekStart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear2;
The formulae for WeekYear1 and WeekYear2 both come from various Qlik sources. Here are the results for the first four days of 2010:
Date Day of Week WeekYear1 WeekYear2 1/1/2010 Friday 1-2010 53-2009 1/2/2010 Saturday 1-2010 53-2009 1/3/2010 Sunday 2-2010 2-2009 1/4/2010 Monday 2-2010 2-2010
The WeekStart() function by default refers to the FirstWeekDay value that has been set previously, and so the week changes on 1/3/2010. However, it appears that the WeekYear() function does not take this into account, and it says, "Hey, Sunday is still in the prior week, and the year on the prior Monday was still 2009!" This produces a bizarre result of January 3, 2010 belonging to the second week of 2009!
Hello everyone This is really great @tseebach . But I have one issue if I use Day-Serial as additional information as soon as I use the FullCalendar... As soon as I'm using it the numbering of my 'Day-Serial' is in the opposite order.
using Full Calendar:
Date
DaySerial
30.5.2021
4
29.5.2021
3
28.5.2021
2
27.5.2021
1
not using the Full Calendar
Date
DaySerial
30.5.2021
1
28.5.2021
2
27.5.2021
3
I like to have the most recent date (30.5.2021) as '1' even if I use the full calendar
DaySerial :
AutoNumber(Daystart([$(_DateField)]),'_DaySerial') as [$(_CalendarPrefix)DaySerial$(_CalendarSuffix)],
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)');
@rwunderlich gave the advice on preceding load with MIN() and MAX()... His example used RESIDENT
I was confused by that; there's no reason to AUTOGENERATE so many rows, you only want 1 single row, with a MIN() and MAX() value
However, @tseebach macro does not get the table name; so can't use RESIDENT $(_DateTable) because you don't know the name of the table where the $(_DateField) comes from!
Also, the MIN() and MAX() will ensure, even if there is an AUTOGENERATE(1000) statement, that only 1 row is generated! I guess that's because MIN() and MAX() are aggregations, and have an implied GROUP; which is all rows; reducing arbitrary number of rows (1, 10, 1000), to a single row.
