none
Different Dimension Beetwen actual and Target Kpi RRS feed

  • Question

  • I have Sales actual value within Sales table that have Time Key in month and I have Sales Target in Sales_Target Table that have key in quarter.  I want to make this data as datasource for actual value and target value for sales KPI but I can compare this data because have different time dimension
    How to solve this problem?
    Tuesday, November 6, 2007 2:41 PM

Answers

  • Hi Hendrik,

     

    I would certainly recommend getting your hands on an MDX book. Getting the most out of your Monitoring server is closely related to your skill level in MDX. There is lots that can be done without MDX skills but the really powerful / cool stuff needs some experience in this area. Have a look at MDX Solutions 2nd Edition by George Spofford - I wrote a review on it a while back.

     

    The MDX you would need for #1 could be something like this:

    Code Block

    (

    [Measures].[TheMeasureThatContainsYourValue],

    [Date].[Calendar].CURRENTMEMBER.PARENT

    )

     

     

    The code assumes that your user has selected a month and that the level above month in your date dimension hierarchy is quarter.

     

    HTH,

    Nick

    Saturday, November 17, 2007 2:06 AM

All replies

  • Hi Henrik,

     

    You may want to have a look into writing some MDX in the metric tuple formula to bring the Actual and Target numbers into the same grain.

     

    Either:

     

    a. Get the value for PARENT of the month member (i.e. the quarter) for the Actual which can then be compared meaningfully against the Quarter-based target

    b. Divide the Quarter-based Target value by 3 to bring the granularity in line with the Actual. Natrually you may want use some more involved logic to weight each month value depending on business requirements.

     

    Cheers,

    Nick

    Thursday, November 8, 2007 9:36 PM
  • Hi Henrik

     

    You should be able to use the time filter formula when you set up the KPI. When you created the data source you would have set up the time mappings on the 3rd tab. This would define what your years/quarters/months are in relation to actual dates.

     

    When you set up the KPI under data mappings you could set the target up with the time filter formula being Quarter and the actual time filter being Quarter.FirstMonth:Month. This should give you the quarter target for the current month and the total of the month to date for the current quarter for the actuals.

     

    Make any sense?

     

    Regards

     

    Paul

    Friday, November 9, 2007 12:41 PM
  •  

    Hi Nick,

     

    How to writing some MDX in the metric tuple formula to bring the Actual and Target numbers into the same grain?

    Please give me the step by step or hyperlink that contain this problem.

    Sorry I'm new in MDX

     

    For solution a. I still don't get the point?

    My solution for now is point b.

     

    Thanks for your help,

    Hendrik

    Monday, November 12, 2007 10:01 AM
  • How about if apply time filter to this problem, is this ok using time filter formula

    Monday, November 12, 2007 10:47 AM
  • Hi Hendrik,

     

    I would certainly recommend getting your hands on an MDX book. Getting the most out of your Monitoring server is closely related to your skill level in MDX. There is lots that can be done without MDX skills but the really powerful / cool stuff needs some experience in this area. Have a look at MDX Solutions 2nd Edition by George Spofford - I wrote a review on it a while back.

     

    The MDX you would need for #1 could be something like this:

    Code Block

    (

    [Measures].[TheMeasureThatContainsYourValue],

    [Date].[Calendar].CURRENTMEMBER.PARENT

    )

     

     

    The code assumes that your user has selected a month and that the level above month in your date dimension hierarchy is quarter.

     

    HTH,

    Nick

    Saturday, November 17, 2007 2:06 AM