Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Creating calendar with multiple dates across multiple tables

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!

Labels (2)
3 Replies
Highlighted
Partner
Partner

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.

Highlighted
Specialist III
Specialist III

 

 

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

Highlighted
Contributor II
Contributor II

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!