Qlikview Scripting Tips: Creating a master calendar

QVMasterCalendarGreat tutorials on creating a master calendar in Qlikview. One of the most common problems to solve in data modeling is that of time: how do you assign month name and other time attributes to a date?  The solution is called a master calendar.

QQQ has some design tips at this post:

Sometimes when working with dates, it is better to keep the date fields outside the fact tables and create them in a table of their own. For instance, you may want to be able to see all dates, not only dates when something has happened. Below are two samples to create a master calendar. The first one contains a hard coded start year. The second one gets the start and end date from the fact table. This means you will always have all dates between the first and last date.

Update: The Qlikview blog has a post on creating a master calendar.

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)…

Matt Floyd‘s insight:

Bonus: great video below I’ve added to this post, from Qlikview: Creating a Master Calendar…

 

Follow

Get every new post delivered to your Inbox

Join other followers: