5 Replies Latest reply: Apr 24, 2012 6:01 AM by Jeroen Veldhoen RSS

    Combining two Master Calendars in one chart

    Jeroen Veldhoen

      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?

        • Combining two Master Calendars in one chart

          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

          • Combining two Master Calendars in one chart

            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