Qlik Community

Ask a Question

Qlik Design Blog

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

Announcements
QlikWorld starts MONDAY! last chance to register is now ! REGISTER NOW
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
ben_skerrett
Contributor III
Contributor III

Thanks for the replies. I found using the concatenate table functionality  solved the issue.

0 Likes
421 Views
yashpace
Contributor III
Contributor III

Hey HIC, 

I am new to Qlik and will appreciate your help a lot,

Can I achieve this using Canonical Dates, 

https://community.qlik.com/t5/New-to-Qlik-Sense/3-Dates-1-Table-1-Master-Calendar/m-p/1622779#M14591...

 

Thanks a ton!!!

0 Likes
395 Views
robert99
Specialist III
Specialist III

Hi Yashpace

You don't need three different master calendars. You need only one and within this one master calendar you would include a common or canonical week, year and months etc.

 

And having all the dates in one table should make it easier to set up (a canonical date). As the issue of using the table with the finer grain does not apply. But make sure this table has a field with a different (unique) reference  for every row in this table.

 

If PKey isn't then create one using say rowno() . But then just follow the instructions in HIC's blog post.

 

Hope this helps.

 

 

0 Likes
385 Views
dwag-SD
Contributor
Contributor

Hello, 
I'm new to Qlik and trying out this code to get a month by month chart with total number of open cases and number of closed cases. So far in my tests I've created a master list for both open and closed dates and tried creating a what I would think a simple chart similar to the "Ordered and Shipped amounts" but to no avail.

My data is simple I feel like I'm almost there but am missing something. Any help would be appreciated. 

NUMBEROrder DateComplete
204452029/10/20187/16/2019
205065869/26/20186/6/2019
2061124910/24/20187/23/2019
2067578811/13/20188/28/2019
2069333911/19/20187/9/2019
2069816011/20/20188/26/2019
2075417612/10/20188/23/2019
2076188612/12/20188/27/2019
2079019912/24/20186/10/2019
208019451/1/20197/15/2019
208045691/2/20196/14/2019
208131151/4/20196/18/2019
208322631/11/20196/17/2019
208357271/14/20197/30/2019
208398481/15/20197/24/2019
208576771/22/20196/11/2019
208712931/25/20196/13/2019
208800541/28/20196/24/2019
208833991/29/20196/24/2019

  

0 Likes
348 Views
abedalraheem
Contributor
Contributor

Hi

I tried to create this example in my work space but I got the below error map_id not found in 

Applymap('OrderID2OrderDate',OrderID,Null()) as CanonicalDate,

why you used  (OrderID2OrderDate) as a table in applyMap function ?

 

thank you . 

0 Likes
299 Views
yashpace
Contributor III
Contributor III

Hi all, is there any alternative to this?  This article has been very helpful but with the amount of data I have, replicating each row multiple times is causing severe performance issues. 

Thanks!

0 Likes
291 Views
imsushantjain
Partner
Partner

As usual Top Notch stuff, thank you Guru🙏

0 Likes
464 Views
alis2063
Creator III
Creator III

Very intresting and Knowlegable  article.

But what does it hold below table 

OrderID2OrderDate

OrderID2RequiredDate

OrderID2Shipping

 

I believe that in the mapping  load will contain somethings like below

OrderID2OrderDate

Mapping Load :

Orderid,

OrderDate

Orders;

OrderID2RequiredDate:

Mapping Load 

Orderid,

RequestDate

Orders;

0 Likes
350 Views
Lily
Contributor III
Contributor III

Hi HIC, 

Great post for my reference. 

But how can I create master calendar with tables are not associate and linked to each other? Based on your example, date that you used was linked which each other using key'Order ID' and 'OrderLineID' where data is related to order. 

In my scenario, I have 3 facts table which are not associate to each other with different key. Actually, I created a data model using other tool and load these 3 tables (PPV, Billing and Sales) in qlikview. That's why I have a problem to link the tables and create the master calendar since the data is not related to each other. Hope you can give me an advice. 

Lily_0-1595296573307.png

Code that I've tried as below: 

DateBridge:
Load PPV_PONO, BUDAT as CanonicalDate, 'Date' as DateType
Resident PPV;

Load Bill_Doc, Bill_Date as CanonicalDate, 'BillDate' as DateType
Resident Billing;

Load Sales_Doc, Sales_DeliveryDate as CanonicalDate, 'SalesDeliveryDate' as DateType
Resident SalesOrder;

/************************ Creating Master Calendar *************************/

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);

Temp:
Load
min( RangeMin( "BUDAT", "Bill_Date", "Sales_DeliveryDate" )) as minDate,
max( RangeMax( "BUDAT", "Bill_Date", "Sales_DeliveryDate" )) as maxDate;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;

TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

CanonicalCalendar:
Load

TempDate AS CanonicalDate,
week(TempDate) As CanonicalWeek,
Year(TempDate) As CanonicalYear,
Month(TempDate) As CanonicalMonth,
Day(TempDate) As CanonicalDay,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as CanonicalMonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as CanonicalQuarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as CanonicalWeekYear,
WeekDay(TempDate) as CanonicalWeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

Thanks.

-Lily-

0 Likes
329 Views
robert99
Specialist III
Specialist III

Hi @Lily 

One option is to concatenate (or merge) the three tables. With one column named for example DataDate. And have the calendar linked to this field. In this way you don't need a canonical date unless one table has more than one date.

0 Likes
324 Views