Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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
Not applicable

Hi Henric,

How do I go about using this concept if all the three date fields are in the SAME Table? I have "Submit Date", "ResolvedDate" and "Re-Opened Date" in the same table. I want a comparison of count of submit vs count of resolved with Canonical Month in X-axis.

Awaiting with anticipation for your expert advice.

Best Regards,

Tony

0 Likes
1,344 Views
hic
Former Employee
Former Employee

You should do the same as above.

If you have all date fields in the same table, you in fact have a slightly simpler problem: You don't need to think much about which key to use for the date bridge table: It should be the field that uniquely defines the records (the primary key) in the table with the dates.

DateBridge:

Load RecordID, "Submit Date" as CanonicalDate, 'Submit' as DateType Resident Table;

Load RecordID, "ResolvedDate" as CanonicalDate, 'Resolved' as DateType Resident Table;

Load RecordID, "Re-Opened Date" as CanonicalDate, 'Re-Opened' as DateType Resident Table;

HIC

0 Likes
1,344 Views
Not applicable

Hi Henric,

Thanks a lot for your suggestion, indeed very helpful.

However i'm unable to get the months in X-Axis, I only get one single bar (as against monthly trend) while using CanonicalDate. Attached is my data model, I would be obliged if you could point where I'm going wrong.

Data_Model-CanonicalDate.jpg

Awaiting your advice.

Best Regards,

Tony

0 Likes
1,321 Views
hic
Former Employee
Former Employee

You seem to have several problems here.

  1. Are you using CanonicalMonth as chart dimension? Are there several values in CanonicalDate when you use this in a list box? (There should be!) If you have several values in the field, but you just have one bar in the chart, then there is a mismatch in how data is linked. (A specific ticket does not link to a specific Canonical Date.)
  2. It seems to me as if the entire data model is wrong: KEY is the Ticket ID, and then this key should not exist in other tables than the Ticket table. The Customer table should not have this key, since one customer can have several tickets, but a ticket cannot have several customers. I would expect the Ticket table to contain a Customer ID and an Assignee ID, etc.

HIC

0 Likes
1,321 Views
Not applicable

Hi Henric,

Thanks for the input:

  1. Yes, Using CanonicalMonth as chart dimentsion, when i use listbox there are several values for        CanonicalDate.
  2. Retrospectively, I have changed the data model, Actually all the fields were in a single table and I had split them into various tables. Please see below my updated(and to say rather simple) data model. However I'm still facing the dame issue, somehow my CanonicalDate is not picking up data.

Could you please point me where I'm going wrong. BTW to make my Master/Canonical calendar, I used the below link from Josh:http://community.qlik.com/thread/48693

Data_Model-Updated.jpg

Awaiting with anticipation for your help.

Best Regards,

Tony

0 Likes
1,321 Views
hic
Former Employee
Former Employee

The model looks OK, and Josh's method to create a Master calendar should work. So, there is something else wrong here.

I would debug just using list boxes. For instance, create a new sheet with the following fields as list boxes: CanonicalMonth, CanonicalDate, DateType, IncidentNumber, IncidentStatus, CustomerCompany. (I.e. fields from all three tables...)

Now, if you select a Month, do you have values in all list boxes? Or are all incident statuses grayed out? And the other way around: If you select a CustomerCompany, do you have a CanonicalMonth possible? If a list box has only gray values, you should be able to figure out where the links break.

HIC

PS. I would keep the original dates in the MasterTicketDetails.

0 Likes
1,321 Views
Not applicable

Hi Henric,

I created the list boxes and here is what I got when selecting a value in "Last Resolved  Date":

  • All the fields in CanonicalMonth are greyed out
  • Two CanonicalDate values are selected. (sometimes one CanonicalDate is selected)

CanonicalDate_Test.jpg

I have used [Last Resolved Date] as min and max value(see below) in Josh's code. Could this be a reason? Can you suggest modifications to make it independent and use a fixed date to calculate min to max here?

Excerpt from the code-

Temp: 

Load 

              min([Last Resolved Date]) as minDate,

              max([Last Resolved Date]) as maxDate 

Resident MasterTicketDetails; 

 

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp;


Also I do have the original dates intact in the MasterTicketDetails!


Awaiting your response.

Best Regards,

Tony


0 Likes
1,321 Views
hic
Former Employee
Former Employee

Yes, you need to find the min and max date among all three dates. E.g.

Temp:

Load       min( RangeMin(  "Submit Date", "ResolvedDate", "Re-Opened Date" )) as minDate,

              max( RangeMax(  "Submit Date", "ResolvedDate", "Re-Opened Date" )) as maxDate

Resident MasterTicketDetails;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

HIC

0 Likes
1,321 Views
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi Henric!

Great discussion, as always! Not to be a nitpick, but you obviously meant to use RangeMax() in the Max calculation...

cheers

Oleg

1,576 Views
hic
Former Employee
Former Employee

You're never a nitpick, Oleg . You're of course right and I changed it.

HIC

0 Likes
1,576 Views