Qlik Community

Ask a Question

Qlik Design Blog

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

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
Henric_Cronström

 

A common situation when loading data into a Qlik document is that the data model contains several dates. For instance, in order data you often have one order date, one required date and one shipped date.

 

Base model.png

 

This means that one single order can have multiple dates; in my example one OrderDate, one RequiredDate and several ShippedDates - if the order is split into several shipments:

 

Logic 1.png

 

So, how would you link a master calendar to this?

 

Well, the question is incorrectly posed. You should not use one single master calendar for this. You should use several. You should create three master calendars.

 

The reason is that the different dates are indeed different attributes, and you don’t want to treat them as the same date. By creating several master calendars, you will enable your users to make advanced selections like “orders placed in April but delivered in June”. See more on Why You sometimes should Load a Master Table several times.

 

Your data model will then look like this:

 

Model with spec calendars.png

 

But several different master calendars will not solve all problems. You can for instance not plot ordered amount and shipped amount in the same graph using a common time axis. For this you need a date that can represent all three dates – you need a Canonical Date. This is how you create it:

 

First you must find a table with a grain fine enough; a table where each record only has one value of each date type associated. In my example this would be the OrderLines table, since a specific order line uniquely defines all three dates. Compare this with the Orders table, where a specific order uniquely defines OrderDate and RequiredDate, but still can have several values in ShippedDate. The Orders table does not have a grain fine enough.

 

This table should link to a new table – a Date bridge – that lists all possible dates for each key value, i.e. a specific OrderLineID has three different canonical dates associated with it. Finally, you create a master calendar for the canonical date field.

 

Full model.png

 

You may need to use ApplyMap() to create this table, e.g. using the following script:

 

     DateBridge:
     Load
          OrderLineID,
          Applymap('OrderID2OrderDate',OrderID,Null()) as CanonicalDate,
          'Order' as DateType
          Resident OrderLines;

     Load
          OrderLineID,
          Applymap('OrderID2RequiredDate',OrderID,Null()) as CanonicalDate,
          'Required' as DateType
          Resident OrderLines;

     Load
          OrderLineID,
          ShippedDate as CanonicalDate,
          'Shipped' as DateType
          Resident OrderLines;

 

If you now want to make a chart comparing ordered and shipped amounts, all you need to do is to create it using a canonical calendar field as dimension, and two expressions that contain Set Analysis expressions:

 

     Sum( {$<DateType={'Order'}>} Amount )
     Sum( {$<DateType={'Shipped'}>} Amount )

 

Bar chart.png

 

The canonical calendar fields are excellent to use as dimensions in charts, but are somewhat confusing when used for selections. For this, the fields from the standard calendars are often better.

 

Summary:

  • Create a master calendar for each date. Use these for list boxes and selections.
  • Create a canonical date with a canonical calendar. Use these fields as dimension in charts.
  • Use the DateType field in a Set Expression in the charts.

 

A good alternative description of the same problem can be found here. Thank you, Rob, for inspiration and good discussions.

 

HIC

166 Comments
Henric_Cronström

@ sk123456 I know you are right about clients wanting to have one calendar only, but I still think that it is a mistake to deliver an app with canonical date only. It is an over-simplification of the problem that leads the user to think that the data model is simpler than it really is.

Users will never understand what a selection in the canonical date really means. They will - sooner or later - ask questions like "Why are there still transactions from February possible, when I have selected March only?" And this will be more difficult to explain than to include several master calendars from the start.

HIC

24,602 Views
mov
Champion III
Champion III

Interesting idea.
In the similar situations, I usually create "common" calendar as a data island.  The Rob/HIC solution should have a better performance, and it allows more options for the end users.

Thanks,
Michael

24,602 Views
carbal1952
Creator II
Creator II

Thanks HIC:

I used this solution from long ago but i always thought it wasn't very "academical".

This post reinforces my idea.

CB.


0 Likes
24,415 Views
michael_solomon
Partner
Partner


Thank HIC, great post again, but...

The date bridge is essentially a concatenation of the granular record X number of times where X is the number of date fields you have, so becomes an extra fact table with more records than the original fact tables Orders and OrderLines. This leaves you with another factor in deciding how much to normalise/denormalise the fact tables. ie. a single fact table will require merging the fact tables (see below), but then have to load in each record X times. Or am I missing something?

Common Date Dimension.JPG.jpg

24,415 Views
kevinpintokpa
Creator II
Creator II

A very well written article!

It may be helpful to provide information including the script command to generate the two mapping tables OrderID2OrderDate and OrderID2RequiredDate.

24,415 Views
valerio_fatatis
Partner
Partner

g o o d ...

0 Likes
24,415 Views
michael_gardner
Creator III
Creator III

Kevin,


I think the two mapping tables should be like below, though I am not 100% sure so please correct me if I am wrong.

OrderID2OrderDate:

MAPPING LOAD

OrderID,

OrderDate

RESIDENT Orders;

OrderID2RequiredDate:

MAPPING LOAD

OrderID,

RequiredDate

RESIDENT Orders;

0 Likes
24,415 Views
Henric_Cronström

@ Michael Solomon    I would not join the two tables. Then the same Amount will end up on three records and the sum will be incorrect. But apart from that you are right that the DateBridge might become a long table.

@ Kevin Pinto and @ Michael Gardner    Michael is spot on with his suggestion on the mapping tables. See more on http://community.qlik.com/blogs/qlikviewdesignblog/2012/09/18/one-favorite-function-applymap

HIC

24,415 Views
Qlik__Vinodh
Partner
Partner

Great post HIC..........Thanks

0 Likes
24,328 Views
Not applicable

Henric Cronström  Yes, I agree with you and somehow it has always been difficult for me to convince them right away.

But now I have your post (and your reply specifically!) to refer to them for better clarity and details....

SK

0 Likes
24,328 Views