Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Employee
Employee

The Master Calendar

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.

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.

Data Model.png

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

32 Comments
MVP
MVP

Henric,

another nice contribution to a very helpful series of blogs.

I think there is one open question that I came across very often in the forum:

What is your suggested approach if one needs to draw several measures based on the different date fields against a common time line? Like I want to compare #  of shipments and orders for the days in October 2012?

Regards,

Stefan

0 Likes
312 Views
Employee
Employee

Stefan

I once faced this problem when I worked in Germany. The customer was an insurance company and the core of the data model was very simple – two tables: paid primes and paid damage compensations. With a date in each table…

The simple solution would have been to link the two tables on dates, but then I would not have been able to use the natural link: Insurance policy ID.

The solution I made was instead to first add one calendar table to each date and in addition also a logical island – an unconnected Master Calendar table. Then I made a bar chart where I used the Month from the master calendar as dimension and two expressions:
     Sum(if(Month=PrimeMonth, PrimeAmount))
     Sum(if(Month=CompensationMonth, CompensationAmount))

This way I could link the Month from the master table to the two different dates and compare received money against paid money. If this is the best solution, I do not know. But still today, I haven’t found any better. I am open to suggestions...

HIC

312 Views
Or
Valued Contributor II

I've used the same approach with some success, Henric - and like you, I've yet to come up with a better option. In my case, I've used it to compare support load (opened cases vs. closed cases), for example - but it gets pretty complicated once you leave the cozy confines of monthly analysis and try to run a daily or even hourly one. This is one of the rare cases where I wish QlikView had "context" joins that would only activate when we wanted them to.

0 Likes
312 Views
mov
Esteemed Contributor III

Most often, I use calendar based on a pre-define date range,
but the definition as a rule is "dynamic".  For example, I want to load transaction data
starting on January 1 of the year three years back, and ending December 31 next
year.  That means, I need two variables
defined on the front end (in input box, or variables overview), e.g. YearsBack
(in my example it will be 3) and YearsForward (1).  In the script, before building the calendar,
define start and end dates:

LET vStart=yearstart(AddYears(today(),- YearsBack));

LET vEnd=yearend(AddYears(today(),+YearsForward));

These variables are used not only to create the calendar,
but also as the conditions for loading the data from the DB tables.

Regards,

Michael

0 Likes
312 Views
Not applicable

Hi All,

I have the data in design level like 2001-02,2002-03......2012-13,2013-14.

The requirement is like i have put next and previou buttons before and after the list box.

and i have to disable the next option for last year is like 2013-14 and disable the previous button like for 2001-02.

Thanks in advance.

312 Views
Not applicable

Very helpful information. I implemented it to my QV module and it works perfect! However, I am currently facing one big problem. If I use date island and use the monthname of the date island as my dimension, then how to aggregate my data over the unlinked dimension?

Thank you so much!

0 Likes
312 Views
Not applicable

Good morning Henric,

I am faced with this same problem, i.e. insurance data is your above solution still the best way?

Regards

Herbert

0 Likes
312 Views
Employee
Employee

No. Today I would instead use a Canonical date. The only drawback with the canonical date is that the DateBridge table can become very large. If this turns out to be a problem, you should instead go for the solution with a logical island.

HIC

0 Likes
312 Views
Not applicable

Hi Henric,

Can you please assist on how to apply a canonical calendar to this data set:

Canonical Calendar.jpg

Thank you

0 Likes
312 Views
Employee
Employee

First, the DateBridge table can be created using:

Load [Policy Number], [Trans Date] as Date, 'Premium' as Type Resident Premiums;

Load [Policy Number], [CTrans Date] as Date, 'Claim' as Type Resident Claims;

Then you create a master calendar on the "Date" field, which is your canonical date.

HIC

0 Likes
312 Views
Not applicable

Thanks for your help Henric,

Almost there, but not quite. The filters from the canonical calendar do not seem to be quite working properly, not quite sure where I am going wrong. Could you you please take a loot at the following:

Script.jpg

Table Structure.jpg

List Box 1.jpg

List Box 2.jpg

Thank you

H

0 Likes
312 Views
Not applicable

The Premium Year list box when I use it filters the data correctly, but the Year list box made from the canonical method doesn't seem to work ok, and returns most of the data from the 6 years.

Pse assist, not sure where my link has gone wrong.

Thank you

0 Likes
312 Views
Employee
Employee

This looks correct to me.

An insurance policy that existed already in 2009 (PremiumYear=2009,2010,2011,...) can have a claim in 2014. And n insurance policy that existed in 2014 can have had a claim already in 2009 (ClaimYear=2009,2010,...).

HIC

0 Likes
312 Views
Not applicable

Hi Henric,

Problem is when I click on 2014 in the year list box, I am expecting to see total premiums for 2014, but i am also getting premiums for transaction dates outside 2014.

Thanks

0 Likes
312 Views
Employee
Employee

Got it. You need to include the year in the key to the date bridge...

But this raises another problem/question: If you click on a ClaimsYear and a Policy number, which PremiumYear do you expect to see? All years when a premium was payed? Or just one year - the year that pertains to the claim?

If it is the former, then you cannot use canonical date in this model. If it is the latter, you need to change your link between the Premiums and the Claims tables. E.g. by using [Policy Number] & Year as Key.

HIC

0 Likes
312 Views
Not applicable

Hi Henric,

Could you kindly demonstrate "...include the year in the key to the date bridge..."

Thanks

312 Views
Employee
Employee

Not until you answer my question: Which PremiumYear or validity period do you expect to see? Or rather: The Premium table contains one record per [Policy Number] and [Validity period]. And a specific claim should only point out one of these record. Not several, as it currently does.

If the answer is that it is defined by the same calendar year, then it is easy. Then you should load Load premiums and Claims using

     Load *, [Policy Number] & Year([Trans Date]) as Key From Premiums (...) ;

     Load *, [Policy Number] & Year([CTrans Date]) as Key From Claims (...) ;

     Drop Field [Policy Number] From Claims;


This way you create a primary key for the Policy+Validity period. But I suspect your answer is more complex. Like "The period defined by [From Date] and [To Date]". If so, you need to create a primary key for exactly this.


Either way, the DateBridge table is created through

     Load Key, [Trans Date] as Date, 'Premium' as Type Resident Premiums;

     Load Key, [CTrans Date] as Date, 'Claim' as Type Resident Claims;

HIC



312 Views
Not applicable

Thanks for your patience Henric,

So the premiums for a given year will not always be one entry, there will be policy additions, adjustments, reversals etc, so each policy number can have several transactions in a year on the premiums side of things.

On the claims side of things, a policy can also have multiple claims in a year.

The sort of result I am looking for for now is a fairly simple one where if I click on a year, I get the values of premiums (including additions etc) that took place in that year, as well as the claims in that year.

I am keeping it simple for now; later we will look at matching policy year to claim, i.e. paying closer attention to renewal periods and periods for which a premium is valid for vs the claims in that period.

For now all I need to come up with is for example how much did we get in premiums in 2013 and how much did we pay out in claims in 2013 (regardless of when policies are valid for)

Hope this makes sense.

( I am glad I cam across someone who has done an insurance problem otherwise I am not sure how I would have explained the problem.

Thanks

H

0 Likes
312 Views
Not applicable

Many Thanks Henric,

I have finally conquered it!

I appreciate your help.

H

0 Likes
312 Views
lawrenceiow
Contributor II

So, I've not got this lovely Calendar Table with many different time periods for a date - Month, Year, Week, Qtr, Financial month number, etc.

I have a chart with a date dimension (MMM YYYY in this case) and there are various list boxes of other dimensions - all non-date related - that users can use to apply filters. However, there are also date related list boxes which I want the chart to ignore. Is there an easy way (I'm guessing I need Set Analysis) to get the chart expression to ignore any and all the dates that appear in the Master Calendar or do I need to specify everything explicitly?

For example:

Sum(Actual)/Sum(Plan) would need to become
Sum({<Year=,Month=,Week=,Qtr=,FinMonNo=,etc, etc>}Actual)/
Sum({<Year=,Month=,Week=,Qtr=,FinMonNo=,etc, etc>}Plan)

I would prefer not to have to put all of that if at all possible. Maybe something like:

Sum({<MasterCalendar.*=>}Actual)/Sum({<MasterCalendar.*=>}Plan)

0 Likes
312 Views
Not applicable

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)

312 Views
lawrenceiow
Contributor II

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.

0 Likes
312 Views
Not applicable

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)

0 Likes
312 Views
sspe_dgs_com
Contributor II

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

312 Views
mov
Esteemed Contributor III

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

312 Views
Not applicable

Thank you very much!!

It is working quite well.

0 Likes
312 Views
mayankraoka
Valued Contributor

Hi Hic,

Awesome explanation....

Regards,

Mayank

0 Likes
312 Views
Not applicable

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,

0 Likes
312 Views
MVP
MVP

aaqureshi2010, you can look into

Canonical Date

in combination with an master calendar.

0 Likes
312 Views
Employee
Employee

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

0 Likes
312 Views