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

[ali_hijazi]

Hello
what if not all Orders (Header) got details?
we link the bridge table to the orders instead of the orders details?

[barnabyd]

G'day @ali_hijazi,

This strategy only works if you join to the table with the lowest granularity. If you join to the Orders table then when you select a date, you won't be able to select only the Order Lines that shipped on that date.

If your data doesn't have Order Lines for every Order, then I think the easiest solution is to create a dummy Order Line row that has a NULL ShippedDate. This provides the linkage from the DateBridge through to Orders, but it won't affect any of the counts or sums based on the ShippedDate.

I'm interested if anyone else has other ways to handle this situation.

Cheers, Barnaby.

[ali_hijazi]

Hello @barnabyd 
there should be other way
adding dummy records is not a "clean" approach

[barnabyd]

G'day @ali_hijazi,

If your data structure is not easy to work with, then you have a choice: either write complex code to handle the difficulties of the data, or "shape" your data into an easier structure to make the code easier.

My suggestion above is what I would normally do - flatten out the data structure to give you a row at the lowest level of granularity. Sometimes I have needed to create "dummy" rows to fill in holes in a difficult data structure.

Here's another idea for you to think about - join the Orders and OrderLines into a single table to give you a single table at the lowest grain.

Cheers, Barnaby.

[krmvacar]

Hi @hic 

ı have a two tables and 2 dates but Canocıcal Date ıs not workıng 

TEST1:
LOAD
KEYFLDPERNUM,
 KEYFLDDATE2,
TARFARK ;

TEST2:

LOAD
KEYFLDPERNUM,
 CHANGEDATE,
RESULT;

tmpDateBridge:
Load
KEYFLDPERNUM,
CHANGEDATE as KEYFLDDATE,
'Order' as DateType
Resident TEST1;

Load
KEYFLDPERNUM,
KEYFLDDATE2 as KEYFLDDATE,
'Shipped' as DateType
Resident TEST2;

NoConcatenate
DateBridge:
Load * Resident tmpDateBridge where IsNull(KEYFLDDATE) = 0;

Drop Table tmpDateBridge;

MasterCalendar:

Load
KEYFLDDATE AS KEYFLDDATE,
Num#(Date(KEYFLDDATE, 'YYYYMMDD')) AS KEYFLDDATENUM,
Week(KEYFLDDATE) AS Week,
Year(KEYFLDDATE) AS Year,
Month(KEYFLDDATE) AS Month,
Ceil(Month (KEYFLDDATE)/1) AS MonthNum,
Day(KEYFLDDATE) AS Day,
Weekday (KEYFLDDATE) AS WeekDay,
'Q' & Ceil(Month (KEYFLDDATE)/3) AS Quarter,
Year(KEYFLDDATE)&'-Q' & Ceil(Month (KEYFLDDATE)/3) AS YearQuarter,
'HY' & Ceil(Month (KEYFLDDATE)/6) AS HalfYear,
Year(KEYFLDDATE)&'-HY' & Ceil(Month (KEYFLDDATE)/6) AS YearHalfYear,
Date(Monthstart(KEYFLDDATE), 'MM-YYYY') AS MonthYear,
Date(Monthstart(KEYFLDDATE), 'YYYYMM') AS YearMonth,
Week(KEYFLDDATE) & '-' & Year (KEYFLDDATE) AS WeekYear;
load
Date(MinKEYFLDate+iterno() -1) as KEYFLDDATE
While MinKEYFLDate+iterno()-1 <=MaxKEYFLDate;

Load
Date(Min(FieldValue('KEYFLDDATE',Recno()))) as MinKEYFLDate,
Date(Max(FieldValue('KEYFLDDATE',Recno()))) as MaxKEYFLDate
Autogenerate FieldValueCount('KEYFLDDATE');

My measures:

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

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

Total value ıs true but monthly value ıs wrong

Can you help me please?

[rmahfoudhi]

Hello,

if i have historical data, each date photo i have the order date and the shipping date 

How can i use the bridge table and how it would be my calendar ?to display at each date photo the number of shipped and created orders ?

and in the application based on the selection on one date which is the date photo i obtain all the order's informations.

Thank you in advance.

[rmahfoudhi]

what if i have a Date Photo(Historical data, a photo each day) with Order_creationDate and close_Date

order_ID	DatePhoto	Create Date	Close Date
4576	29/03/2023	14/11/2022	14/03/2023
6666	29/03/2023	01/02/2023
7777	29/03/2023	01/03/2023
4576	30/03/2023	14/11/2022	14/03/2023
6666	30/03/2023	01/02/2023
7777	30/03/2023	01/03/2023	12/03/2023
9565	30/03/2023	30/03/2023
9999	30/03/2023	30/03/2023

To create a single date to manage all the orders created, closed and In progress(where len(Close_Date )=0, not closed yet) 

I have a single Table and i don't see clearly how to add the canonical calendar  

thank you in advance.

[WaltShpuntoff]

This is where you need the Date Bridge table. This would consist of order_ID,  CanonicalDate, and DateType.

CanonicalDate links to a date table that is used for making selections. order_ID links to your orders and Date Type is which type of date you care about in your expression,

You will need to generate the bridge table.

DateBridge:

Load

order_ID,

DatePhoto as canonicalDate,

'DatePhoto' as DateType

resident tableName

Where Not isnull(DatePhoto);

repeat this process for the other date types, and concatenate each time.

For create date metrics use the set modifier DateType={'CreateDate'} or whatever you tagged it with.

[ioannaiogr]

Please have a look at my issue regarding canonical date here issue with canonical date implementation . Thank you

[Hein_Schultz]

I've been doing this but never knew it had a specific name. Came across this post preparing for the Data Architect exam. Thanks for the post though, it's well explained.