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
hic
Former Employee
Former Employee

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
597 Views
cbushey1
Creator III
Creator 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
597 Views
hic
Former Employee
Former Employee

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
630 Views
cbushey1
Creator III
Creator 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
630 Views
hic
Former Employee
Former Employee

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
630 Views
cbushey1
Creator III
Creator 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
630 Views
Anonymous
Not applicable

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
630 Views
hic
Former Employee
Former Employee

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
630 Views
Anonymous
Not applicable

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
637 Views
Anonymous
Not applicable

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