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

154 Comments
sanjyotpatkar
Contributor III

3) How do I obtain only the tickets created for a particular month based on the Opened date? I used to get this calculated using set analysis while using canonical calendar with the DateType field..what is the kind of set analysis that I can use to calculate the count of tickets created for a particular month and resolved for a month?

0 Likes
315 Views
robert99
Valued Contributor II

Thanks for the reply

I can understand if you have a table without an expression (only dimensions) this issue might arise. But as soon as a expression is added count({<CanonType = {OrderDate}>}OrderNum) then this problem disappears doesn't it

" number of orders by each of the above dates."

I have never come across an issue using canonical dates. in your example I would simple set up three expressions

count({<CanonType = {OrderDate}>}OrderNum)  = OrdersRec

count({<CanonType = {ActualShip}>}OrderNum) = OrdersShipDateEst

count({<CanonType = {PlannedShip}>}OrderNum) = OrdersShepDateAct

If the user wants to show the actual shipping dates for orders received in Aug 2015. All I would do is use this expression count({<CanonType = {OrderDate}>}OrderNum)  and use a dimension of

month (ActualShippingDate)

Maybe Im missing the point but I have yet to come across an issue as explained above.

0 Likes
315 Views
robert99
Valued Contributor II

Thanks for your reply

"user has selected a specific quarter/month/week but he will still see orders that were placed before his selection"

Not if he/she uses an expression as explained above.

I see you as a Qlik Guru as the way you think re Qlik and do things is usually 100% in alignment with me. I have followed totally many of you recommendations. Including the basics of your BoM recommendation although I changed it a lot. This canonical calendar was just what I was looking for. Its brilliant. Perfect for me (I used date Islands a lot before).

But on this I still think you are not only wrong. But its a bad way to do this (although I may see the light one day). I have seen users produce the wrong figures by using the wrong calendar. And as  a user I have done it myself. Its just far too confusing with lots of calendars. Its a pain when on most occasions thanks to Canonical Dates it can be avoided

I overcame this issue in VIEW by using triggers. By removing all calendar selections when entering and leaving a sheet. And only giving users access to the right calendar for a sheet. But using triggers is a pain.

But this is not possible in SENSE. Just yesterday I had a user ring up and ask why a count was wrong. Reason. even though I gave a non CanonDate calendar an odd name he still used it. And he is a very enthusiastic user of Qlik.

0 Likes
315 Views

Sanjyot

It seems as if your question demands a long, elaborate answer, so I suggest you post it in a thread of its own. That way you will increase visibility and get many good answers and suggestions. 

Concerning the Master Calendar: There are many good posts about master calendars in the forum, so I suggest you search there. I usually the following structure - but with more fields in the preceding Load:

// ========= First load your table with the Date field


MinMaxDate:
Load   Num(Min(Fieldvalue('Date',RecNo()))) as MinDate
   Num(Max(Fieldvalue('Date',RecNo()))) as MaxDate 
   Autogenerate FieldValueCount('Date');

Let vMinDate  = Peek('MinDate',0,'MinMaxDate');
Let vMaxDate  = Peek('MaxDate',0,'MinMaxDate');

Let vStartDate = YearStart(vMinDate)-1;
Let vEndDate  = Floor(YearEnd(vMaxDate));

Calendar:
Load   Year(Date)         as Year,
  
Ceil(Month(Date)/3)   as Quarter,
   Month(Date)           as Month,
   Date;
Load  Date($(vStartDate)+RecNo()) as Date
   Autogenerate vEndDate - vStartDate ;

0 Likes
315 Views

If you have multiple dates in your data model, you will have misunderstandings whatever solution you choose. You're damned if you do, and you're damned if you don't. So, I am fine with whatever solution you choose.

My point is only that I think it is easier to explain if you you have the explicit dates as well as the canonical date. Then the user will eventually learn. But if you don't have the explicit dates, the user will never understand what goes on behind the curtain.

Further, with your method, the user can never make a selection like "ordered in July, but shipped in September". It is just not possible.

315 Views
sanjyotpatkar
Contributor III

Thanks Henric. I have been following your blogs for most of my development activity and your suggestions have always guided me correctly. I have tried to use both the approaches i.e. canonical as well as generation of reference dates. The only issue with this is that it takes long time to execute the scripts.. I did post my initial query in the forum but didnt receive any response on it.

0 Likes
315 Views
cbushey1
Contributor III

hic‌,

I wanted to reach out to you since you seem to be the expert on Date table (among many other things). I have a scenario that I have been struggling with and was hoping for your opinion on where to go. I am leaning towards this Canonical approach to having one master calendar but wanted thought you could shed some oversight on my data model and the best approach.

I have two calendars in my data model. One is the master calendar which is created at the sql level and brought in as a table and the other is an Account Summary Calendar generated using Qlik code from a field in the Account Summary table. Account summary contains 1 row for each accountID and month. It holds summary level data on that accounts revenue.

My master calendar is based off the events table (eventDate) and is much more granular. The event table sort of acts as the date bridge you have outlined as it has all the dates throughout the rest of the data model in event date with an event name that corresponds to that event.

We want the user to select year(s) or month(s) from the master calendar and return information that is housed at the account summary level. Right now this will not work. Here is what our current data model looks like (part of it at least).

If you could let me know where you think I should go next to get at having one filter cascade down both events and Account summary based on that year or month I would appreciate it.

datamodel_community.png

0 Likes
315 Views

This is tricky...

First of all, you could claim that the summary data is redundant - it is probably just a summary of what you already have in other tables. And as such it doesn't really belong in the same app... But I realise that you (or the users) want it there for e.g. comparison purposes, so may perhaps not be an option to remove it.

So, I would then probably regard it as a "second fact table" with different granularity than the Activity table, which I guess is the "real" fact table. The solution would be to concatenate the two fact tables (AccountSummary and Activity) and use the same keys (when appropriate). See Fact Table with Mixed Granularity and Generic keys. In a way, you can claim that making a monthly account summary is an activity.


One problem is however that you don't have a date in your activities table. One solution could be to join the Activities table with the Events table before you concatenate the AccountSummary onto it. Another solution could be to add extra records to the Events table - basically an ActivityID that would link all dates to the relevant records in the AccountSummary part of the fact table.


Another problem is that there are keys in the Activities table that don't exist in the AccountSummary table. You would need Generic keys to solve this.

Bottom line: Changing the data model to solve this could work, but it would need a lot of work and involve many challenges.

So, perhaps a completely different approach is better: You could leave the data model as it is, and display measures in charts using if()-conditions. For example, say that you have a chart with month as dimension, and you want to display both Count(CaseID) - which is linked to the event date - and Sum(ActualRevenue) - which is linked to the SummaryMonth.

Then you could probably use SummaryMonth as dimension and

Count( If(SummaryMonth=Date_Month, CaseID ) )

Sum(ActualRevenue)

as measures. But verify that the numbers are right. Using if() this way sometimes leads to record duplication and incorrect numbers.


Good Luck


HIC

0 Likes
315 Views

I just realized...

There could exist a completely different solution: If you create a composite key of Account and YearMonth, in your Activity table, then you could use this to link your AccountSummary to this instead of to the Accounts table. 

I assume that an Activity always belongs to a specific month. If not, you can probably do the same thing to the Events table instead.

HIC

0 Likes
315 Views
cbushey1
Contributor III

Thank you for the quick and thorough response hic‌.

I think changing the data model at this juncture is going to be a tough sell since we just added this summary table into the existing structure. The summary information wasn't already being captured elsewhere in the data model and that is why we needed to add it.

I like your idea of creating a composite key of AccountID and YearMonth as this will make each row have a unique key that can then be joined to either Activities or Events.

If I understood correctly, I would make this key in my Account Summary table using the fields (accountId and SummaryMonth) and in either Events or Activities table I would make this key using AccountId and Date_YM (from the Master calendar). Thus giving me the same values to link on.

0 Likes
315 Views

Yes, you understood correctly. Whether the link should be in the Events table or the Activities table probably doesn't matter - both contain the necessary information. I would pick the smaller of the two.

HIC

0 Likes
315 Views
cbushey1
Contributor III

hic,

I tried this approach and hooked up my data to the Events table. What I later realized is that not every account has an activity on it. The Act summary table has information like budget that aren't showing up now that the table has moved because no event or activity for that matter has occurred on the account. Is it possible to make the link between my Events table and my Account summary table include all values from both tables or do I need to explore the path of data model optimization and Generic keys as you suggested previously?

0 Likes
315 Views

You could probably add dummy activities, e.g. something along the lines of

Activities:

Load *,

  AccountID, YearMonth,

  AccountID & '|' & YearMonth as ActivityMonthID

  From Activities (...);

// ----------- Dummy Activities

Concatenate (Activities)

Load distinct * Where not Exists(ActivityMonthID);

Load

  AccountID, YearMonth,

  'Dummy' as Description,

  AccountID & '|' & YearMonth as ActivityMonthID

  From AccountSummary (...);

AccountSummary:

Load *,

  AccountID & '|' & YearMonth as ActivityMonthID

  From AccountSummary (...);

Note the "Where not Exists". One challenge is that you have Activities and Events in different tables. So, either you join these, or you create dummy events as well as dummy activities.

HIC

0 Likes
315 Views
cbushey1
Contributor III

hic‌,

Thanks this makes alot of sense. After messing around with this for a little bit I was able to get the calendars functioning the way I would expect. Early testing looks promising. One thing I found is that when I combined my Events and Activities table than added the dummy activities, I seemed to have lost a significant number of rows. It looks like the distinct statement is the culprit. Taking it out yields a higher number than I would have thought. Can you elaborate on what that whole line is doing?

Thanks for all the help, I really appreciate it.

0 Likes
315 Views

Yes, the distinct clause can have that effect.

As a work-around, you can add a record counter in your Activities table, "RecNo() as RecNo". Then no two records will be the same and the distinct will not change anything.

Or you can load the dummy records in two steps, where the second step doesn't have a distinct clause:

tmp:

Load distinct * Where not Exists(ActivityMonthID);

Load ... From AccountSummary (...);

Concatenate (Activities) Load * Resident tmp;

Drop table tmp;

HIC

0 Likes
315 Views
cbushey1
Contributor III

Just wanted to say thanks for all the pointers and guidance on this one. I think I have things working the way I would need them to and will be testing it out in the coming days.

Chase

0 Likes
315 Views
sanjyotpatkar
Contributor III

Hi Henric

I have used the Canonical Calendar in my script since I need to display 3 expressions against one time axis i.e. months i.e Created vs Resolved vs Outstanding Tickets.

The charts are displayed perfectly as along as the Month is entered in the dimension displaying months that have no data as well but now I need to display data only for last 13 months. So I modified the dimension to if(Month<=addmonths(monthname(today(),-13),Month) . As a result the months with blank values disappear from the graph. Can you please suggest what is going wrong when I restrict the dimension to only 13 months? I have unchecked on 'Suppress Zero & Missing values' already

0 Likes
315 Views

The reason is that months with no data will not pass your test. I would try to use the corresponding test in the script instead, so that you create a month that only is populated the last 13 months. E.g.

if(Date>=AddMonths(MonthStart(Today(),-13),Month(Date)) as LastMonths

0 Likes
315 Views
sanjyotpatkar
Contributor III

When I add this in the canonical calendar script I get a circular loop. I included this in the below script.

SUB CalendarFromField(_field, _calendar, _prefix)
[$(_calendar)]:
// Generate Final Calendar
LOAD
[$(_field)]
,
year($(_field)) as [$(_prefix)Year]
,
MonthName([$(_field)]) as [$(_prefix)Month]
,
day([$(_field)]) as [$(_prefix)Day]
,
WeekStart([$(_field)]) as [$(_prefix)Weekstart]
,
if([$(_field)]>=AddMonths(MonthStart(today()),-13),month([$(_field)])) as LastMonths
;

also I didnt understand why the missing months disappear when the months are restricted to 12. When I consider the full data set the missing months get populated.

0 Likes
315 Views
sanjyotpatkar
Contributor III

Sorry my syntax issue..

I did add to the script and it shows last 12 months but again the missing months gets suppressed.. Is there anything that I am doing incorrectly.I added LastMonths to my Dimensions and my expressions are count(DISTINCT{<DateType={'Created_PR'},Ticket_Type=>}[Problem Number]) for Created and etc.

LOAD
[$(_field)]
,
year($(_field)) as [$(_prefix)Year]
,
MonthName([$(_field)]) as [$(_prefix)Month]
,
day([$(_field)]) as [$(_prefix)Day]
,
WeekStart([$(_field)]) as [$(_prefix)Weekstart]
,
if([$(_field)]>=AddMonths(MonthStart(today()),-13),month([$(_field)])) as LastMonths
;

0 Likes
315 Views
sanjyotpatkar
Contributor III

Thanks Henric..The same script worked..I dont know why it failed earlier when I posted my earlier response..I ran it again and worked when i turned on 'Show all values'

0 Likes
315 Views
Not applicable

Nice one Henric

0 Likes
315 Views
vadim_grab
Contributor

Dear, Henric.

I  am looking for solution same problem. My data model has 3 tables: Facts, CustomerMonthlyPlans and MasterCalendar. MasterCalendar - The Fastest Dynamic Calendar Script (Ever) which generated on DateID Field (Facts Table)

Can you  show me on my example how I can linked Month in Calendar to see Customer Plan and Actual both.

Thanks a lot for any answer.

D_QlikView_test3.qvw 2017-01-05 18.31.28.png

0 Likes
315 Views

You can use the three tables you already have, but you need to change the key between "Facts" and "CustomerMonthlyPlans". Use

   Customer_ID & '|' & MonthStart(DateID) as CustomerMonthID

instead.

In the Plan table, you must make sure that you get the same formatting of the date in the key, for example

   Customer_ID & '|' & MonthStart(PlanMonth) as CustomerMonthID

or

   Customer_ID & '|' & MakeDate(PlanYear, PlanMonth) as CustomerMonthID

depending on the format of PlanMonth.

HIC

0 Likes
315 Views
vadim_grab
Contributor

Henric,

Many thanks for so fast answer. "Everything of genius is simple!"

But there is some problem -  MasterCalendar generated on Facts field Date (Date with  Actual). Some Customers who didn't have Actual at December 2016 don't have CustomerMonthID so some Customers Plans not displayed  (Some Customer has  a MonthPlan but don't linked with MasterCalendar Month).

What should I do in this case?

Many thanks for your answer.

0 Likes
315 Views

There are a couple of different ways to solve this.

  1. Add dummy records to the Facts table: Give every customer an additional transaction of 0 every first of the month. Then the current data model will work fine. See Product not exists - add manual values zero or Generating Missing Data In QlikView
  2. Concatenate the actual numbers with the plan numbers into one single fact table. Use generic keys to solve the problem of different granularity in the different tables (the Actual numbers have dates, and the Plan numbers have months, and you want both to link to something relevant in the calendar). See Generic keys.

HIC

315 Views
vadim_grab
Contributor

Henric,

Exactly what is needed!!! I prefer the second option.


Thanks a lot!


P.S. Please add me to your contacts network

0 Likes
315 Views
vadim_grab
Contributor

Henric,

Table Facts include  DateID, Customer_ID and Actuals

Concatenate (Facts)  // Table CustomerPlans

LOAD

Floor(PlanMonth) As DateID ,   // PlanMonth has format DD.MM.YYYY (Dec = 01.12.2016)  AutoNumber(Customer_ID, 'Customer') as Customer_ID,

Num(Plan) As Plan



Working...

0 Likes
315 Views
amirboutayeb
New Contributor

Hello,

I don't understand why in that example you use OrderDate and RequiredDate in the ApplyMap instruction (and before in the LOAD MAP) but you don't do the same thing with ShippedDate.

I am facing the same situation and i don't know which dates i should load directly and which ones i should load by the ApplyMap Instruction.

Thank you

0 Likes
315 Views
robert99
Valued Contributor II

"i don't know which dates i should load directly"

Example

If there are two dates. One in a call table (initial call) and one in a  FSR table (field service request) So there is only ever one row / entry for one call (Call table = SCCall). But there can be many FSRs for one call (FSR table = SCFSR). The Call table and FSR table are joined by the Call_Num.

So the 'Date bridge' must join to the FSR table (finer grain) not the call table

//link table for required dates

DateBridge:  // FSR table

Load

CallFSR_LINK,     // join to the FSR table. This = Call_Num & FSR_Num

VisitDate AS DateC,   // Canonical Date

'Visit' as DateType      // to use in set analysis

Resident SCFSR

;

DateBridge:   //call table

Concatenate (DateBridge)

Load

CallFSR_LINK,  // join to the FSR table

ApplyMap ('MAPCALLINDATE'    ,Call_Num    ,'NoDate') as DateC,  //canonical Date from SCCall

'Call' as DateType     // to use in set analysis

resident SCFSR ;

NB If all dates are in the same table then load direct

0 Likes
315 Views