Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
hic
Former Employee
Former Employee

 

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

185 Comments
glennmaldonado23
Contributor II
Contributor II

Hi

 

What does mean 'OrderID2OrderDate' for the apply map function? Applying technique for my example I receive errop map_01 not found

 

 

395 Views
anderseriksson
Partner - Specialist
Partner - Specialist

Name of a mapping table previously loaded in the script with the "mapping load" statement.
The script engine can't find the table, presumably either you've named it differently or you forgot the 'mapping' prefix in the load statement.

376 Views
glennmaldonado23
Contributor II
Contributor II

Logically I understand very well the needed task but problem with the qlik language. 

I have to create a dashboard which shows a canonical calendar which show the total quantity of items, which have a begin and end of guarante date respectively, are outside of the selected date-year. ( I created alredy the master calendar and bridge) 

Time Lapse is from 2012 - 2028 with a monthly step. 

Basically Condition is the following: 

Count ( If ( BeginGuarantee < StartMonthYear and EndGuarantee > EndMonthYear) 

For example: 

Item BeginGuarantee EndGuarantee
1 01/Jan/2014 01/Jan/2016
2 01/April/2014 01/April/2016
3 01/July/2014 01/July/2016

 

desired Result: 

Month - Year Items_in_Guarantee
Jan 2014 0
Feb 2014 1
March 2014 1
April 2014  1
May 2014 2
June 2014 2
July 2014 2
August 2014 3
September 2014 3
Oktober 2014  3
318 Views
marcus_sommer

This is a task for an Intervalmatch respectively an internal while-loop like already suggested here:

Date interval - Qlik Community - 2104531

A canonical calendar could become useful if there are several date-fields which have a certain relationship to each other, for example in a chain of events like order --> sales --> bill --> shipment dates. A from-to time-span doesn't belong to this logic - at least not directly else after the resolution of them to a single real-date this might become part of such a chain if it's needed.

To clarify - master-calendar(s) and a canonical calendar as well as as-of-tables and any intervalmatch-tables respectively other link-tables are logically different things which shouldn't be mixed up even if they are later combined in any usage.

Helpful is often not to start with the origin data-set and requirements else just a few dummy-records which are then step by step increased to more details. 

299 Views
anushree1
Specialist II
Specialist II

Hi,

I do not understand what OrderLines table contains here.

My understanding is that Orderlines table is as below,

anushree1_0-1692963017526.png

In such case it only has shipped date , when so how can we derive required and ordered dates from it like 

 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;

 

Please let me know if i have missed understanding something here

283 Views