none
Dynamic Target Values in a Scorecard RRS feed

  • Question

  • My actual value in my KPI is  a measure from my cube. I am using time intelligence at the dashboard page  to get WTD for the current week. I would like to have my target value be WTD from last year using Time intelligence at the dashboard page.

    This can be done Hardcoded in the KPI in the TI entry, However this doesn't seem to be ideal for what I am trying to accomplish.

    Can This be done?

    Additionally

    I really would like to have a Generic scorecard with KPI's and pass them the Actual and Target values timeframes like the scenario above.

    Currently, I have a scorecard set-up with 10 kpis and Actual and target values pointing to measures in a cube. I am using TI  for LYMTD,MTD,LYQTD,QTD Etc..  This is a good generic solution to see the results independently, However My last year MTD is the actual value which needs to be compared to MTD for this year.

    So now I have two sets of target values that are comparing to a different set of actual values.

    Visually I can probably get rid of the target values and show the actual values side by side to see the difference but I will be missing the indicators.

    The real issue here is that I am going to have to have separate scorecards for each time period.

    So instead of having one scorecard with the KPI as rows and Date( LYYTD,YTD,LYMTD,MTD) as columns in the scorecard. I now have to have separate scorecards, Month, Quarter, Year Etc and the same set of 10 KPIS set up for all my different slice scorecards.

     

    Any suggestions in design will be appreciated.

     

    Thanks,

    Larry

     

    Friday, May 29, 2009 3:32 PM

Answers

  • Have the filter generate/filter the date portion on the columns of the scorecard and then within the KPI or the actual calculation in the cube that you are using utilize the CurrentMember reference to get at the information within the ParallelPeriod function and the reference would be something like this for the previous period:

    ParallelPeriod( [Date].[Fiscal].[Fiscal Year], 1, [Date].[Fiscal].CurrentMember )


    When you filter for the day, week, qtr, and year this will return the information for the same time period for the parallelperiod. You just need to include the time period on the columns.

     

     


    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    • Edited by Dan English Thursday, June 4, 2009 10:48 AM
    • Marked as answer by LarryBiStack Thursday, June 4, 2009 3:10 PM
    Thursday, June 4, 2009 10:44 AM

All replies

  • Sounds to me like you need to implement what we would consider a shell dimension in the SSAS solution if you are using SSAS to get at the different time periods that you want to evaluate dynamically like this for comparison.  I don't believe using the time intelligence filter in the data source for the KPIs will help you out here, but that might be one option to explore.  Here are a few things to look at in regards to add time intelligence into SSAS - A different approach to time calculations and DateTool.

    As far as the other design option you can modify the setup of the targets in the scorecard design to simply display the actual value and still show the indicator.  Then you can remove the actual and rename the target to get a more condensed scorecard which will be a little easier for analyzing and doing comparisons.
    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    Sunday, May 31, 2009 11:43 AM
  • Larry:

    I needed to do the same thing for a customer!

    What was able to do is create the KPI within the cube using the appropriate time factors.

    For instance Last YTD to Current YTD
    ...
    Last Fiscal MDT to Current Fiscal MTD
    ...

    Once these were built into the cube presentation as KPI's on the dashboard were extremely simplified without having to determine or hardcode any dates or relationships.

    Eric S Blake
    Sunday, May 31, 2009 7:22 PM
  • Eric,

     

    The issue I’m having deals with the presentation of the data. I am trying to only have one KPI like Net Sales and show it across different measures like MTD, WTD Etc.

     

    So I want to have the Kpi on rows, and show different columns like MTD,QTD,WTD.

    The Actual vs Targets is really semantics of whether I put it in a KPI or using a MDX Tuple custom formula in the PPS KPI.

     

    I am really looking to use a parameter or overload to have only one KPI per measure

    and upon rendering in the scorecard it will show it differently across the columns for my period in time like YTD, MTD Etc.

     

     

    This can be done if just using the Actual and targets from my cube with the Time intelligence in PPS pointing at MTD, YTD, WTD Etc.

    The thing is that you can’t change the target value to point at a different target for the KPI like last year. If you change this in the MDX Tuple you now are hardcoding a value that  points at a specific measure and the generic solution won’t work anymore.

     

    So what I really need is a way to have the target value accept a formula for the KPI based on which time intelligence filter has been selected.

     

     I am guessing this isn’t supported since I haven’t got an answer on this?

     

    Thanks,

    Larry

    Monday, June 1, 2009 4:44 PM
  • I am half way there now. I am able to use current member and prev member in the MDX tuple this allows my Time intelligence filters to work properly across Day,WTD,MTD,QTD and YTD. This is for my current vs previous time period scorecard.


    However I am still trying to come up with a solution to compare the current time period to the same period last year. The issue here is that ParallelPeriod needs the level of the date Hierarchy and the lag and this would have to be hardcoded in the MDX Tuple in PPS.

    Any Suggestions on This?

    Thanks,
    Larry


     

    Wednesday, June 3, 2009 1:10 PM
  • Have the filter generate/filter the date portion on the columns of the scorecard and then within the KPI or the actual calculation in the cube that you are using utilize the CurrentMember reference to get at the information within the ParallelPeriod function and the reference would be something like this for the previous period:

    ParallelPeriod( [Date].[Fiscal].[Fiscal Year], 1, [Date].[Fiscal].CurrentMember )


    When you filter for the day, week, qtr, and year this will return the information for the same time period for the parallelperiod. You just need to include the time period on the columns.

     

     


    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    • Edited by Dan English Thursday, June 4, 2009 10:48 AM
    • Marked as answer by LarryBiStack Thursday, June 4, 2009 3:10 PM
    Thursday, June 4, 2009 10:44 AM
  • Hi Dan thanks for the reply.

     

     I don't have 13 months of data in the cube yet so I really can't test this, but I just want to make sure I have a good understanding

    of your solution.    My time intelligence filter will have the periods in time set-up like Day, WTD, MTD,QTD,YTD. My KPI will have an actual value called SamePeriodLastYear with a MDX tuple like you described ParallelPeriod( [Date].[Fiscal].[Fiscal Year], 1, [Date].[Fiscal].CurrentMember ),

    My target value will be called currentperiod and have a MDX tuple like ([Order Date].[Retail Calender Hierarchy].Currentmember,[Measures].[Gross]).

     

    My dashboard will have the time intelligence date filter  being used  on the columns of the scorecard.

     

    So by putting the year portion of the hierarchy  with a lag of 1 as the parallel period, this will allow Time intelligence to derive the correct time periods for everything lower down in the Hierarchy, like months, weeks, etc.

     

     

    Thanks,

    Larry

    Thursday, June 4, 2009 1:44 PM
  • One thing that seems to be missing is that if I use this

    ParallelPeriod

    ( [Order Date].[Retail Calender Hierarchy].[RC Year Num], 1,

    [Order Date].[Retail Calender Hierarchy].

    CurrentMember)

    in my MDX tuple in my KPI then the thing that is missing is the measure i want for the parallelperiod. I am not an mdx expert so maybe you can help.

    I tried specifying the measure as the third paramater in  a tuple but I got an error. Is this allowed.


    Thanks,
    Larry

    Thursday, June 4, 2009 2:39 PM
  • It would look like this:

    (ParallelPeriod ( [Order Date].[Retail Calender Hierarchy].[RC Year Num], 1, [Order Date].[Retail Calender Hierarchy].CurrentMember) , Measures.<MEASURE NAME>)


    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    Thursday, June 4, 2009 2:49 PM
  • It was giving me an error that way, I got it to work like this. It seems to be giving the correct results.


    ([Measures].[Gross],

    ParallelPeriod

    ( [Order Date].[Retail Calender Hierarchy].[RC Year Num], 1,

    [Order Date].[Retail Calender Hierarchy].

    CurrentMember))

    Thursday, June 4, 2009 2:54 PM
  • Can u please expalin the detailed synatx above.I am new to MDX and have a urgent delivery
    Where does [Order Date ] stands and [RC Year Num] is a member of hierarchy or... do we need to go drill up in a hierarchy while using the parallelperiod fuction in mdx pls help
    Thursday, September 17, 2009 11:00 AM
  • [RC Year Number] is the retail calendar year level within the retail calendar hierarchy of the Order Date dimension.  So in this example he is return the previous year's value for whatever member in the retail calendar hierarchy he is currently looking at.

    ParallelPeriod (MDX)
    http://msdn.microsoft.com/en-us/library/ms145500.aspx


    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    Thursday, September 17, 2009 12:53 PM
  • Hi Dan,

    My dashboard has two filters, one is calendar(
    Time Intellegent filter
    ); the other is the list of time calculations and one Analytic grid.

    On the Analytic grid, columns are selected
    Time calculations such as “YTD”, “YTD year ago, etc. and the customer hierarchy is on rows. How
    can I make the calendar selection be a currentmember, so when a user selects 2/18/2012,
    then YTD will be calculated to 2/18/2012?

    The following is the query I am using

    SELECT

    { {<<TimeCals>>} * {[Measures].[Sales Amount USD], [Measures].[Gross Margin USD]}} ON COLUMNS,

    HIERARCHIZE( { DESCENDANTS( [Business
    Unit].[Business Unit].[All], , AFTER ) } )
    ON ROWS

    FROM [Invoice History]

    I cannot figure out how to make the selection on the Calendar filter to be the Currentmember of the time calculation.

    Thank for your help!

    Hua Yang



    CELL PROPERTIES VALUE, FORMATTED_VALUE,
    CELL_ORDINAL, FONT_FLAGS, FORE_COLOR, BACK_COLOR









    Thank you very much for your help.





    Hua Yang



    hyang

    Sunday, February 19, 2012 3:35 PM
  • Have you tried using <<SourceValue>> in your filter link formula? Source values represents the current member unique name.

    Next time please create a new thread instead of reviving a 3 years old post. Makes it easier for the community to identify and respond effectively.

    Thanks.


    http://dailyitsolutions.blogspot.com/

    Tuesday, February 21, 2012 4:35 PM