Master Calendar to be linked to several unrelated tables

[hansdevr]

Management of the firm I'm working for has asked me to build a Management KPI dashboard, in which a number of tables should be loaded, part from Excel, part from QVD. These tables have no definite factual relationship. All have two datefields in common, Year and Quarter. They want to control ALL tables at once from two list boxes which - you guessed it - contains both Year and Quarter.

Of course this could be done through variables and buttons to change values of the latter, in combination with set analysis, but I think this is a visually rather unelegant solution..

Any suggestions on how to fix this? Use a Master Calendar with only Quarter en Year values? Or are variables the only way to go?

Here is the data model. As you can see, totally unrelated...

[hansdevr]

Update:

I read that one solution COULD be to concatenate all tables and rename Year and Quarter fields to one common name and link the MC to those fields... Sounds logical...

Is this the same answer all you gurus would come with?

[avinashelite]

Other way would be to create a link table and connect all the tables

check this Doc for more details

Concatenate vs Link Table

[marcus_sommer · Accepted solution]

If there is no further connection than year and quarter I wouldn't concatenate these tables else I would create one small master-calendar (with year and quarter and apply an appropriate renaming of these fiedls within the other tables) which would work here like a link-table. Whereby I'm not sure that your users will get all their wanted insights with this kind of datamodel.

- Marcus

[hansdevr]

I just dropped the quarter field in all tables and made them into one composite key YearQrt, containing YYYY-Q#.

Then I did a simple MC containing only YearQrt and separate, calculated year and quarter fields.

No concatenation needed, indeed!

Thnx, Marcus!!