Skip to main content
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

192 Comments
ali_hijazi
Partner - Master II
Partner - Master II

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

1,357 Views
barnabyd
Partner - Creator III
Partner - Creator III

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.

1,330 Views
ali_hijazi
Partner - Master II
Partner - Master II

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

1,231 Views
barnabyd
Partner - Creator III
Partner - Creator III

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.

1,117 Views
krmvacar
Creator II
Creator II

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)

krmvacar_0-1662200829874.png

 

Total value ıs true but monthly value ıs wrong

Can you help me please?

 

872 Views
rmahfoudhi
Partner - Contributor III
Partner - Contributor III

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.

 

 

700 Views
rmahfoudhi
Partner - Contributor III
Partner - Contributor III

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.

662 Views
WaltShpuntoff
Employee
Employee

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.

639 Views
ioannaiogr
Creator II
Creator II

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

552 Views
Hein_Schultz
Partner - Contributor
Partner - Contributor

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.

452 Views