Using Multiple Dates With Master Calendar

[#1]

Dear All,

I am trying to associate dates from multiple tables with master calendar.

Scenario:

Suppose we have two tables.

The first table connects with the second table using a Key field

The first table also connects with the master calendar but using the date1 field.

The second table also connects with the master calendar using the date2 field.

What is the best way to avoid loops while still being able to use master calendar?

Please find the document attached.

Regards & Thanks,

-Khaled

P.S:

Note1: Not all tables are inter-connected, so it's a loosely-connected data model and some tables may remain isolated.

Note2: Tables may or may not be connected to each other but date filters from each table should show all dates in the date fields (which is why the master calendar is in place)

[hic · Accepted solution]

I would start by removing the calendar completely and get the other 7 tables linked properly first. I think that there are tables or keys missing...

Then I would add one calendar per date. Why? See here.

Finally, I would add a generic calendar for all dates. This can be tricky to achieve, but usually it is possible to link the most detailed level of the facts, e.g. order line, to multiple dates using a link table. The link table will then in turn link to the generic calendar. In other words, the link table should have three fields: OrderLineID, DateType ('Payment date', 'Invoice date', 'Order date', etc.), and Date.

HIC

[#3]

Hello Henric,

This is it. I initially assumed that I would have to load the master calendar multiple times but wasn't any sure if it would be the best approach.

Thank you once again

Regards,

-Khaled.

[#4]

Hi Henric,

Thank you for your solution,I'm experiencing the same problem...I've linked all my tables correctly but I can't seem to get the the Master Calendar working with the 3 dates...Can you please attach a sample to further explain your solution.

Looking forward to hearing from you

Regards

Eugene

[hic]

Well, do you really want to connect the master calendar to all three dates? It would mean that when you click on "February", you get orders that were placed in Feb (OrderDate), and orders that were ordered muche earlier but payed in Feb (InvoiceDate), and orders that were ordered earlier and payed later, but required in Feb (RequiredDate). Not very simple and clear for the user...

Read this before you decide: http://community.qlik.com/blogs/qlikviewdesignblog/2012/08/30/master-table-with-multiple-roles. My recommendation is to have one calendar per date.

The question is whether to have a "generic calendar" in addition. A generic calendar needs a bridge table, created through e.g. (or more complicated if the dates reside in different tables)

Load 'OrderDate' as Type, OrderID, OrderDate as GenericDate From Orders ;

Load 'InvoiceDate' as Type, OrderID, InvoiceDate as GenericDate From Orders ;

Load 'RequiredDate' as Type, OrderID, RequiredDate as GenericDate From Orders ;

And then you connect your Master Calendar to the GenericDate.

HIC