none
Show KPI trend based on custom MDX calculation RRS feed

  • Question

  • I have a trend indicator that is set Increasing is Better, Band using stated score (advanced). 

    My problem is that I would like the scorecard with this KPI to be linked to a month filter in the dashboard:  The target and actual values for the KPI work perfectly with the filter, but the trend will not display at all unless I put months on the rows under the KPI and then link the filter to the rows.   Is there any way to show the trend with the KPI without putting month on the scorecard?  I don't want months on the rows, and even if I DO put them there, the trend indicator won't roll up to the top level.

    I have entered the following custom MDX for the data mapping of the stated score.  This MDX returns 1 (up) if the measure grows more than .01 from the previous month, 0 (flat) if growth is between -0.01 and 0.01 and -1 (down) if growth is less than -0.01.  It works as a “WITH MEMBER” as a measure in a test MDX query.

     

    iif(([Vw Dim Time].[Year -  Mth - Date].currentmember,[Measures].[Test]) - ([Vw Dim Time].[Year -  Mth - Date].currentmember.prevmember,[Measures].[Test])<-0.01,-1

     ,iif(([Vw Dim Time].[Year -  Mth - Date].currentmember,[Measures].[Test]) - ([Vw Dim Time].[Year -  Mth - Date].currentmember.prevmember,[Measures].[Test]) < 0.01,0

     ,1))

     

    I hope this makes sense.  Any help is appreciated!

     

    Friday, May 1, 2009 9:58 PM

Answers

  • I ended up moving this calculation as a calculated member within the cube, which then worked without a problem.  Did not try moving the date reference to the columns as Dan suggested, but that may have worked too?
    • Marked as answer by Lisa LRB Monday, May 11, 2009 6:07 PM
    Monday, May 11, 2009 6:07 PM

All replies

  • This needs a reference to your date hierarchy that you are referencing with the currentmember.

    Might want to take a look at this posting for a reference - Creating a Trend Indicator for a KPI in a Scorecard

    You could put the date reference on the columns and then hide it and be able to reference this with your date filter if that is what you are doing.  That should work.



    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    • Edited by Dan English Tuesday, May 5, 2009 2:17 AM added hiding option
    Tuesday, May 5, 2009 2:15 AM
  • I ended up moving this calculation as a calculated member within the cube, which then worked without a problem.  Did not try moving the date reference to the columns as Dan suggested, but that may have worked too?
    • Marked as answer by Lisa LRB Monday, May 11, 2009 6:07 PM
    Monday, May 11, 2009 6:07 PM