Qlik Community

Qlik Design Blog

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

Canonical Date

 

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

153 Comments
birkirbjorns
New Contributor III

Hi All,

How would change the model to handle a second fact table, with a new "datetype"?

Regards

Birkir

0 Likes
578 Views

In principle the same way.

First of all, if you have two fact tables, then I would recommend that you concatenate them into one table. That is is most cases the best solution. The primary key of this table can then be used for a canonical date.

If you instead use a link table, you should use the primary key of the link table.

HIC

0 Likes
578 Views
birkirbjorns
New Contributor III

Hi HIC,

Thank you for your reply. Yes I could concatenate the tables, would you then just create some sort of sequence (uniqueID per row) as a primary key?  But what if we would add a third facttable and the fourth, just the same ?

Regards

Birkir

0 Likes
578 Views

You could use a unique ID, yes. E.g. RowNo() as ID.

But more likely, you know your data so well so you know which keys you need to uniquely specify a date. It could be a combination of the other keys you already have, e.g.

     Autonumber( OrderLine & IsBudget & BudgetMonth ) as Key

Note that some keys (BudgetMonth in my example) may well be NULL for some of the data.

HIC

0 Likes
578 Views
Not applicable

Hi Henric,

Thanks for this Post!, Working from long time on QLikView, but things only got a catch when they exactly require. So your post given the idea to show trend line using CanonicalDate for Shipped Qty and Received Qty. Even I am using Master calendar separate which is linked on Order Date. but  Master calendar only set the Variable value and I can take that Date as Current date and easy to show MTD Values.... Hope It will help someone to get what exactly can be done.

Thanks

Regards

0 Likes
578 Views
Luminary
Luminary

Here's a 'AsOf' Calendar script which makes set analysis very simple which may also be of use and can be used in conjunction with this method.

http://community.qlik.com/docs/DOC-6593

Regards

Richard

QlikCentral.Com

0 Likes
578 Views
Not applicable

Hi Henric

Simply amazing how you explain those topics in easily, understandable words!

Another great post that gets to my favourites...

Brgds,

Anthony

0 Likes
578 Views
Partner
Partner

Hello,

Its' very interesting but I have a problem.

How can I do if I have 2 shipped dates for 1 orderline ?

It doesn't operate and I can't join the orderline table in the orders table because I have amounts in the two tables.

Thanks for your answer.

Yoann

0 Likes
578 Views
Not applicable

Hello Henric,

thanks for the explanation.

But how do I proceed if I want to create such a calendar with data that is not "linked"?

In your example you have a Shipping Date, Order Date and Required Date, but everything belongs to the same Order.

I have the following problem as I am trying to connect transaction data of customers with their registration and activation into one calendar:

There are two databases for our customers, one which contains two dates, the one at which the customer registered and the one at which he became active.

In the second database there are the transactions he has been doing over the time.

I am now trying to put this into one calendar so I can see -by filtering-, how many customers registered in a certain month, as well as how many transactions were processed.

I think the problem here is that there is only 1 date of registration resp. activation for a customer but there can be more than one date of transaction.

My code looks like this:

DateLink:

Load

  fk_merchant_id,

  month(DateReg) as Month,

  year(DateReg) as Year,

  'Reg' as DateType

Resident DB1;

Load

  fk_merchant_id,

  month(DateAct) as Month,

  year(DateAct) as Year,

  'Act' as DateType

Resident DB1;

Load

  fk_merchant_id,

  month(transactionDate) as Month,

  year(transactionDate) as Year,

  'TRX' as DateType

Resident DB2;

When I filter for a month it shows me the correct Registrations and activations but the TRx Data is accumulated until that very date.

Thank you for any adivise.

Benjamin

0 Likes
578 Views

You need to link your DateLink table to the Transaction table via a Transaction_ID and not via the fk_merchant_id. Then it will work.

HIC

0 Likes
578 Views
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
578 Views

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
578 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
578 Views

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
578 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
578 Views

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
578 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
578 Views

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
578 Views
MVP & Luminary
MVP & Luminary

Hi Henric!

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

cheers

Oleg

578 Views

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

HIC

0 Likes
578 Views
Not applicable

Hi Henric,

Thanks a lot for your restless help.

Unfortunately even after changing the min and max dates the CanonicalMonth is not linking to the MasterTicketDetails table.  I'm getting the same issue as I had listed in my last screenshot (multiple CanonicalDates show values while CanonicalMonth is grey when a ticket is selected). Could difference in date format play any role here? I tried a lot of permutations and combinations of changing my code, but none came to my rescue.

Hence I'm inserting the my code, as I'm sure you could pick up where I'm going wrong much easily.(unable to attach the file hence pasting the long code below, apologies for the untidy work.)

MasterTicketDetails:
LOAD [Incident Number],
Description,
Notes,
Resolution,
[Customer Login ID],
[Customer Company],
Region,
[Customer Organization],
[Customer Department],
[Customer Site],
Impact,
Urgency,
[Incident Priority],
[Service Type],
[Categorization Tier 1],
[Categorization Tier 2],
[Categorization Tier 3],
[Product Categorization Tier 1],
[Product Categorization Tier 2],
[Product Categorization Tier 3],
[Support Company],
[Support Organization],
[Support Group],
Assignee,
[Incident Status],
[Status Reason],
[SLM Status],
[Resolution Categorization Tier 1],
[Resolution Categorization Tier 2],
[Resolution Categorization Tier 3],
[Cause Code],
Submitter,
[Submit Date],
Year([Submit Date]) as SubmitYear,
QuarterName([Submit Date]) as SubmitQuarter,
MonthName([Submit Date]) as SubmitMonth,
[Responded Date],
[Last Resolved Date],
Year([Last Resolved Date]) as ResolvedYear,
QuarterName([Last Resolved Date]) as ResolvedQuarter,
MonthName([Last Resolved Date]) as ResolvedMonth,
[Closed Date],
[Reported Source],
[Individual Transfers],
[Group Transfers],
[Re-Opened Date],
[Customer Name],
[Service Line],
IF(Len([Last Resolved Date])=0,('12/31/2014'-[Submit Date]),([Last Resolved Date]-[Submit Date])) As Age,
If(Len([Last Resolved Date])=0,'Open','Resolved') as Status

FROM
MasterTicketDetails.qvd
(
qvd);

Load
[Incident Number],
If(Age<=7, '0-7 days',
If(Age>7 and Age <=14,'8-14 days',
If(Age>14 and Age <= 21, '15-21 days',
If(Age >21, '21 and more days'))))as Bucket

Resident MasterTicketDetails;

DateBridge:
LOAD [Incident Number], [Submit Date] as CanonicalDate,'Submit' as DateType
Resident MasterTicketDetails;

LOAD [Incident Number], [Last Resolved Date] as CanonicalDate,'Resolved' as DateType
Resident MasterTicketDetails;

LOAD[Incident Number],[Re-Opened Date] as CanonicalDate, 'Reopened' as Datetype

                Resident MasterTicketDetails;

//CODE FOR CREATING MASTER CALENDAR//

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

Temp: 
Load 
min( RangeMin( "Submit Date", "Last Resolved Date", "Re-Opened Date" )) as minDate,
max( RangeMin( "Submit Date", "Last Resolved Date", "Re-Opened Date" )) as maxDate 
Resident MasterTicketDetails; 

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;

//

Would really be obliged if you could help me find the niggle.

As always waiting with much anticipation on your remarks and suggestions.

Again, thanks a lot for the help thus far.

Best Regards,

Tony

578 Views
  1. The variable assignment "max( RangeMin( ..." needs to be changed to RangeMax. (I had an error initially in my previous response.)
  2. I would check that the dates really are integer dates (no fractional time). See Why don’t my dates work?; second bullet.

HIC

0 Likes
578 Views
Not applicable

Hi Henric,

FINALLY!! It works just the way it should be!

The tables were not linking because of the time stamp, it contained fractional time. Using the steps in the document I was able to convert them into integer.

Thank you so much for helping me out in this. For a two weeks old QlikView newbie your timely inputs have been very encouraging and helped me discover a lot and I'm falling in love with this tool.

Looking forward to learning more from you out here.

Best Regards,

Tony

0 Likes
578 Views
Not applicable

Hi Henric Cronström,


I have one doubt. Master calendar is must to create a canonical date?


Thanks,

Pramod

0 Likes
578 Views
Not applicable

Hi all,

Anyone help me to create a canonical date for the below data in "Qlik Sense".

In below data, I need canonical date for, date_created, date_completed and date_signed.

LIB CONNECT TO 'MyDB';

LOAD `invitation_id`,

    `client_id`,

    `batch_meta_data_id`,

     activated,

     `date_created`,

    `date_activated`,

     `campaign_name_id`;

SQL SELECT `invitation_id`,

    `client_id`,

    `batch_meta_data_id`,

    activated,

    `date_activated`,

    `date_created`,

    `campaign_name_id`

FROM rcdbrpt.invitation;

LIB CONNECT TO 'MyDB';

LOAD `course_completion_id`,

    `invitation_id`,

    `final_test_status`,

    `course_completed`,

    `date_completed`;

SQL SELECT `course_completion_id`,

    `invitation_id`,

    `final_test_status`,

    `course_completed`,

    `date_completed`

FROM rcdbrpt.coursecompletion;

LIB CONNECT TO 'MyDB';

LOAD `invitation_id`,

    `user_signed`,

    landing_page_accessed,

    `date_signed`;

SQL SELECT `invitation_id`,

    `user_signed`,

    `date_signed`,

    landing_page_accessed

FROM rcdbrpt.signupactivitylog;

Advance Thanks,

pramod

0 Likes
578 Views
Partner
Partner

hic‌ Thanks a lot. This was a solution I was looking for.

But if we don't have all the OrderIDs in OrderLines table, then those OrderDates and RequiredDates will not get added to the DateBridge table right? Is there a way to solve it too?

0 Likes
578 Views

If you don't have the OrderID in the OrderLines table, then you have other problems: Then you don't have the necessary link...

First, you need to find the table with the finest grain. This does not have to be the OrderLines table. There could be a table with even finer grain, e.g. the InvoiceLines table. Then you should use this table.

Secondly, you may need to use Applymap() to move a date into the Load statement of the data bridge table. This way you can move a field from any table into this Load statement.

HIC

0 Likes
578 Views
Not applicable

Would it be able to construct a canonical calendar in this setup:

Issue1.png

The utilization calendar gives the utilization of different aircraft. Whereas the CalenderTaskPerf gives the dates on which a maintenance task has been performed on the aircraft. Now I came across an issue when constructing graphs, that I want to plot the utilization in relation to performed tasks in one graph.

But of course they have both two different dimensions, since there are two different calendars. I cannot directly link the calendars because it will create a loop. What links both these calendars is the event_perfno_ii. For a certain date where there is a task performed there doesnt have to be a utilization date, but it is possible..

So is it even possible to make use of canonical calendar?

0 Likes
578 Views
daniel_kusiak
Contributor II

Great post. Thank you.

0 Likes
578 Views

Dutchsky

If an "ac_registr" can have several "UtilizationDates", or an "event_transferno_i" can have several "TaskPerfDates", then you will indeed have problems creating one single Canonical Date. There just is no field that you can use as key.

It could perhaps be possible if you change the data model, though. If you join the tables and create a composite key based on the primary keys of Utilization and TASK_CHECK_LIST, then it should be possible. (However, if you have a many-to-many relationship between the two, this model would be inefficient.)

HIC

0 Likes
578 Views