Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
hic
Former Employee
Former Employee

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

34 Comments
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
5,850 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
5,850 Views
hic
Former Employee
Former 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
5,841 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
5,841 Views
hic
Former Employee
Former 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
5,841 Views
Not applicable

Hi Henric,

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

Thanks

5,841 Views
hic
Former Employee
Former 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



5,841 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
5,841 Views
Not applicable

Many Thanks Henric,

I have finally conquered it!

I appreciate your help.

H

0 Likes
5,770 Views
lawrenceiow
Creator II
Creator 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
5,770 Views