Creating calendar with multiple dates across multiple tables

[Russ]

Hi - I have multiple tables, some linked together on keys, others not, but want to be able to make a calendar that links all of the tables by date (day, week, month, year, MTD etc).

The structure is:

Think it is a master calendar, but also read about canonical dates and getting confusing - any help appreciated!

Cheers!

[semlemmers]

Hi,

I would try one of (the second option is the preferred option)

1. Create a date island, not linked to the date model. Then use set analysis in your front end. When you make a selection, you can have each object show the data based on the date(s) you need.

This could over complicate your expressions and maintenance is high. And, not to forget, can result in poor performance for user experience. 

2. Concatenate your fact tables and make sure you have matching keys (google Star-model vs. snowflake). For example, load orders once with OrderDate as primary key (AS %DateId) and then again with DeliveryDate as primary key (AS %DateId). Then link the master calendar to %DateId. When making a selection in your master calendar, data from both fact tables are shown, but both with different results.

Normally I would choose this option, because this allows for much simpler expressions and usually has better performance.

[robert99]

A master calendar is just a calendar (Year, Month etc) for a date. It should really just be called say a Date Calendar. And then Master Calendar could have been used to link to a Canonical date.

A canonical date is really needed (there is a non-script Date Island alternative but I wouldn't recommend this) when you have more than one date in one table if you only want one (master) Calendar.

If you have many dates in different tables. Canonical dates sometimes works. But care is needed as detailed in this Canonical Date thread (but also read the comments). If it doesn't work then concatenating tables and then using Canonical Dates if needed  should work.

Or an alternative if only one date per table. Just concatenate the tables and use one common date field. Then a Canonical date is not needed.

https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578

[Russ]

Thanks you both for the suggestions!

My fact tables are different in their structure so difficult to concatenate, so not sure if that will work as option 2 above. Will read through the canonical date article and have a go.

Thank you!

[khadar]

Hi Robert,

Could you able to find any solution for your query?. I have same situation here, if you have solution for it, would you bale to help me.

 I have 4 diffrent tables, but i cannot concatenate tables but need something which enables the link for all dates.

[vikasmahajan]

you can create link table refer detail demo & documentation 

https://community.qlik.com/t5/Qlik-Sense-Documents/link-table/ta-p/1883375

Hope this helps

Vikas