The Master Calendar

[hic]

One of the most common problems to solve in data modeling is that of time. How to assign month name and other time attributes to a date. The solution is called a master calendar.

[hic]

A master calendar table is a dimensional table that links to a date in the data, e.g. OrderDate.  The table usually does not exist in the database, but is nevertheless needed in the QlikView application for a proper analysis. In the master calendar table you can create all time and date fields that you think the user needs; e.g. Month, Year, Quarter, RollingMonth, FiscalMonth or flags like IsCurrentYear, etc.

A typical master calendar tables contains one record per date for the time period used in the QlikView app, perhaps a two-year period, i.e. 730 records. It is in other words a very small (short) table. Since it is small, you can allow yourself to have many fields in it – it will not affect performance in any significant way.

There are in principle three ways you can generate the records (with an infinite number of variations in the details):

• Load from the fact table, e.g.
  Load distinct Date, Month(Date) as Month … resident TransactionTable ;
• Generate all dates within a range, using autogenerate, e.g.
  Load Date, Month(Date) as Month … ;
  Load Date($(vStart) + RecNo()) as Date autogenerate $(vEnd) - $(vStart) ;
• Generate all dates within a range, using a while loop, e.g.
  Load Date, Month(Date) as Month … ;
  Load Date(MinDate+iterno()) as Date While iterno() <= MaxDate - MinDate ;
  Load Min(Date)-1 as MinDate, Max(Date) as MaxDate resident TransactionTable

In the first case you use the table to which you are going to link the master calendar. This way you will get exactly those values that really exist in the database. Meaning that you will also miss some dates – e.g. Saturdays and Sundays most likely - since they often do not exist in the database.

In the second case, you generate a range of dates. This is a good solution, but it means that you will need to define the range beforehand. There are several ways to do this, e.g. find largest and smallest value in the data; or hard-code the days for the relevant year.

In the third solution, you generate all dates between the first and last date of your transaction table. This is my preferred solution. Optionally you can use YearStart(Min(Date)) and YearEnd(Max(Date)) to define the range.

The word "Master" for the calendar table is really misleading. There is no reason to have only one calendar table. If you have several dates, you should in my opinion use several calendar tables in the same data model. The alternative - to have the same calendar for all dates - is possible using a link table but complicates the data model and limits how the user can make selections. For example, the user will not be able to select OrderMonth=’Sep’ and at the same time ShipperMonth=’Nov’.

Bottom line: Use a calendar table whenever you have a date in your database. Use several if you have several dates.

HIC

[#3]

Take a look at this Community post: Ignore all selections except some specific fields using Set Analysis

In summary, your expression will look like the following excerpt:

sum({$<[$(=Concat({1<$Field-={'Stock','Manager'}>}distinct $Field,']=,[')&']=')>} Amount)

[lawrenceiow]

Thanks mhd, that helped. I found a post by steverimar, within the link you gave which was actually what I was after.

So, to ignore all selections made to any field in a specified table (MasterCalendar) I used:

{$<[$(=Concat({1<$Table={'MasterCalendar'}>}distinct $Field,']=,[')&']=')>}

Thanks again, Michel, for pointing me in the right direction.

[#5]

hic Do you have an example for an autogenerate script within a specific range? I have several Date tables for every single date type but I don't want a DateBridge. In my opinion I only need the script to generate the master dates...

For example a Master Date table with dates from 01.01.1980 to 31.12.2035 (format dd.mm.yyyy)

[sspe]

I think you can find several different examples of how to create a Date table, but here is one suggestion:

set vDateMin = '01-01-1980';
set vDateMax = '31-12-2034';

LOAD
date(date#('$(vDateMin)')-1 + recno())  as Temp_Date
AUTOGENERATE (date#('$(vDateMax)')-date#('$(vDateMin)'))+1;

You can then just define you start and end date and then you have a table with a base date.

Regards

Steen

[Anonymous]

Check my 6-year old document here: How to create a Calendar

[#8]

Thank you very much!!

It is working quite well.

[mayankraoka]

Hi Hic,

Awesome explanation....

Regards,

Mayank

[#10]

Hi Henric,

Can I use the concept of Master Calendar in the case where I have to merge three dates into one date in qlikview? E.g. I have order date, start date, and finish date and I have to merge all of these three dates into one date called the implementation date, can I use the master calendar to resolve this?

Thanks,

[swuehl]

aaqureshi2010, you can look into

Canonical Date

in combination with an master calendar.

[hic]

The post about the Canonical Date that Stefan suggest will certainly help you. You should also read the post that explains the background: Why You sometimes should Load a Master Table several times‌.

The concept in both these posts, is that one single "master" calendar cannot solve the problem. But by using several calendars, you can solve all problems around multiple dates.

HIC