Reusability QlikView: Time/Calendar Table

Question

It is important to apply calculations and business rules consistently across QlikView applications. We can store variables, connections etc. in an external file and apply them across various QVW.

Is there a standardized script for time/calendar dimension that has practically everything you need regarding time, and which could be used accross different QVWs without having to spend time to create it all over again when developing a new QVW. I would like to have something that is robust, has everything I need and that I can include in every QVW.

Answer 1

You can check for the Rob Wunderlich's Qlikview Components, there is a standard Calendar function you can call.

You can also check on his website, there's a very good script I use each time I make a report. You can put the result of the script in a QVD and load it on every report you make.

So It will be something like this:

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
 AddYears(today(), -6) as mindate, // The first date you want
 Today() as maxdate
AUTOGENERATE 1;

STORE MasterCalendar INTO 'Calendar.qvd' (qvd);
DROP TABLE MasterCalendar;

Answer 2

Two options:

1. You can create a .qvw that generates a calendar .qvd that contains all of the date fields (say DayOfMonth, DayOfWeek, Month etc.) as well as a Date field, called say CalendarDate. Then in all of your .qvw you can Left Join your fact table against the data loaded from the .qvd through the CalendarDate field. For performance reasons I would not leave the calendar as a separate table, if I can help it.

2. Otherwise you can create a text file that contains the column definitions in a variable that you can use as macro. Something like

   LET CalendarFields = '
       Year($1) as DateYear, 
       Month($1) as DateMonthOfYear,
       Week($1) as DateWeekOfYear,
       Day($1) as DateDayOfMonth,
       WeekDay($1) as DateDayOfWeek,
       Date(MonthStart($1), ''YYYY MMM'') as DateMonthInYear,
       Year(WeekStart($1)) & ''w'' & Num(Week($1), ''00'') as DateWeekInYear ';
   

   You can load this file, say common.txt with $(Must_Include=common.txt); or $(Include=common.txt); Then in your load statement for your fact table, you can use this macro like like:

   Facts:
   LOAD
       *,
       $(CalendarFields(FactDateField));