Canonical Date

[hic]

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.

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:

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:

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.

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 )

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

[glennmaldonado23]

Hi

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

[anderseriksson]

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.

[glennmaldonado23]

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

[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. 

[anushree1]

Hi,

I do not understand what OrderLines table contains here.

My understanding is that Orderlines table is as below,

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

[ss2q]

How about if I have two tables order lines and invoice? I have ordered the date in order line table and gl_date in the invoice table. I have already extracted from this each date in each table because I want to make an analysis using Year. So, how to relate the year of order date and year of invoice date

[robert99]

@ss2q 

This becomes a bit tricky

• I assume the 2 tables are linked by the fields order number & order line number (or part number maybe)
• You usually have to link the Canonical to the invoice table. As 2 or more invoices might be done for one Order+ Line combination. NB I often do this by setting up a new Field field like InvTable  & RowNo() as (say) LinkField in the invoice table
• The issue is that Order table entries (rows) might not have an invoice table entry. So you need to add (concatenate) missing Order entries (rows) to the invoice table 
• You can do this using where not exists (bit tricky) or OtherSymbol (very easy). Heres a quick example below on how to do both

//Other Symbol To add the missing join field number or reference (+) and  NoEntryQlik (or whatever) as required for other fields 

• Set OtherSymbol = +;   
  Concatenate (CalTCode)
  LOAD * Inline [
  CalT_Code,Call_Catagory,CallType,CalT_Resp_Hour
  +,NoEntryQlik,NoEntryQlik,NoEntryQlik
  ];
•  
•  
• whereas 'where not exists' is a bit trickier. This example is a fact joining a fact table to ensure canonical dates work. (the date calendar is joined to SCFSR). I did this before I found the (excellent) OtherSymbol solution
  
  Call_Num2 (a temp field) is added to the SCFSR table but not the SCCall tablen (it won't work using SC_Call as 'where not exists' checks all previously loaded tables).
  
  CALLSMissing:
  Concatenate (SCFSR)
  Load
  //level 2
  Call_Num ,
  Call_Num2 ,
  etc
  WHERE NOT EXISTS (Call_Num2) ;
  //level 1
  load
  Call_Num ,
  Site_Num ,
  Call_Num as Call_Num2 ,
  etc
  Resident SCCall
  ;
  drop FIELD Call_Num2 ;

[ss2q]

@robert99 

My both tables have order no in common so could you please let me know how the code will be?

I want to use invoice dates gl_date in my analysis and more specifically the year from it. So I want it to match with year of Ordere date?

[robert99]

@ss2q 

The Orderline table and OrderInv table usually have more than just an OrderNum in common. Usually it also has the orderLine Num in common as well. Or a partNum.

NB, I usually Associate (join keep) the Order header table and OrderLine table by the OrderNum. But the Orderline table and OrderInv table by a created field like 

OrderNum & '|' & OrderNum as LinkField_XXX1  

Then I join the OrderInv table to the CanoniclaBrige table by something like OrdInv & '|' & rowno() as LinkFieldXXX2 

[ss2q]

@robert99

II understand you are right but for now I just want to test with these could you help me with this post. I tried making it but couldnt do it. I will be grateful if you can

https://community.qlik.com/t5/Featured-Members/I-want-help-with-master-calender/td-p/2426206#M261