Combining two Master Calendars in one chart

[#1]

Hi all,

I am struggling with the following issue:

I have a central table with invoices. Two different master calendars are linked to this table. One is linked to to the invoice date and the other is linked on two other fields using intervalmatch.

Now I would like to create a chart with one dimension 'YearMonth' and four different expressions. The issue is that I would like to base one expression on master calendar 1 and the other 3 on master calendar 2. So in reality, I have Calendar1.YearMonth and Calendar2.YearMonth.

How can I build expressions in such a way that it can use both master calendars in one object with the use of one single dimension?

[#2]

Hi Jveldhoen

Are you saying the end result is a chart that has one dimension, yearmonth and then expressions based on different date dimenstion fields?

Probably the most efficient solution would be to re-structure your data model and have the invoice table as a large fact table, with 1 date field, but I can see why that would be difficult!

Without changing the data, however  this could be possible using if statements.

If you create a dummy field, standing alone in a table with yearmonth (eg with an inline) you could use this as your dimension and use if statments to match the data to it.

EG say you inline load a table with one field yearmonth with the values: 2010-01, 2010-02,.... etc

You would then use this as the dimension and enter embedded if statements, such as:

sum(if(calender1.Yearmonth = [dimension yearmonth],your expression here) )

What this will do is search through calendar1, and whereever the yearmonth matches the dummy yearmonth, it will sum the values.

Warning: this is really clunky as the table will not be linked!

Regards,

Erica

[#3]

Hi Erica,

Thanks for your reply! I agree that changing the data model is the optimal solution, but this is probably not feasible in the amount of time I have given myself...

I am currently using your proposed approach, it is working sufficiently

Regards,

Jeroen

[#4]

thanks Jveldhoen - I was in a similar position myself working to a deadline and didn't want to risk restructuring the "main" data incase it didnt work! (It was a V large dataset)

Once the deadline had gone, I put it right and re- posted the dashboard on the portal. Glad it worked for now

Erica

[johnw]

This example probably has more than you need, but it demonstrates one way to create a single calendar linked to multiple dates in multiple tables.  This calendar is additive, so doesn't require you to change your current data model (unless the new tables cause loops - a possibility).  In some cases it might be better to more fundamentally alter the data model, but this gives an alternative.

If you have much data, a sum(if()) can end up performing poorly, which is why I would prefer a data solution.  But it sounds like you have it working, and I assume it's performing fine for you, so you're probably fine.  If so, the attached example is just something to consider if you encounter this sort of thing in the future.

[#6]

Thanks John, helpful as always!