Answered by:
Incorrect trend indicator
Question

The value for the performance indicator is evaluated in the underlying system, which passes a value of 1, 2, or 3 for performance to date. This in turn determines whether the stop light is red, yellow or green.
In the cube we also have a Trend calculation,which calculates the difference between the current and previous value for performance. The resulting values are 2, 1, 0, 1 or 2. This ties up neatly with the trend indicator that has 5 arrows and the values have been banded accordingly, using >2.5, 1.5 to 0.5, 0.5 to 0.5 etc as the ranges.
One of the KPIs is returning a calculated trend value of 1, but is showing the trend arrow for the +1 value. I have checked that the KPI is properly mapped and that the banding has been defined properly (as above).
I can't think what else to check. Does anyone have any ideas/experience of this?
Thanks in advance.
Tuesday, July 22, 2008 3:51 PM
Answers

I was having the same issue and what I noticed that Excel 2007 KPI trend value displayed in the formula bar was 0.5, but it showed the graphic for a value of 1. I changed the value in my KPI trend expression to return 0.4 and the problem worked fine. I'm guess there's an error in the display logic in Excel 2007.
Monday, September 22, 2008 1:40 PM
All replies

Looking into this further, I edited the Trend column to show the Actual value, which returns the value as reported in the Actual column for that KPI (which is fair enough). I then edited it to show the Target value, thinking it would show the value of the Trend calculated value (as Trend is a 'Target' in DD terminology), but all it is showing is '1' for every KPI.
This doesn't look right. Surely it should show the Trend value calculated in the cube?
In fact, looking at other trend indicators there are no downward pointing arrows, even though other KPIs have a negative value for Trend calculated in the cube.
I'm wondering if this feature can't cope with negative numbers?
Any thoughts anyone? (Microsoft?)
Wednesday, July 23, 2008 9:49 AM 
Well, part of the above was caused by defining the Trend Target as fixed value of 1, so that is easily explainable.
But the rest isn't.
I modified the the calculation of Trend in the cube to avoid negative numbers, by adding 3 to the current value, before subtracting the previous value. So the range of values is now 5,4,3,2,1. I changed the KPI banding accordingly.
But for the KPI above, and some others I have looked at, a negative trend (i.e. a down pointing arrow) gets 2 added to the value and converted to an upward pointing arrow. The cube is reporting the correctly calculated trend figure, so the scorecard is doing something to the calculation of this value before attaching the trend indicator (but seemingly only for arrows which would otherwise point downwards  KPIs with up and horizontal arrows have the right trend calculated value shown and the right arrow).
Wednesday, July 23, 2008 10:47 AM 
I've done some more work  most values stored for Trend are wrong (the only correct ones are those that should be the maximum possible value)
And even where a wrong value is not at maximum, it still gets the upward pointing arrow (i.e values of 4 and 3 are still getting the best performance arrow for trend when they should be either at an angle or horizontal).
I used the Bulk Edit facility to set the banding. Could that be affecting it?
Monday, July 28, 2008 10:17 AM 
I am having exactly the same problem. I have created a Calculated member (MDX) that returns values between 1 and +1 and the Trend is always the smilly face. I can use the calculated member in the browser and see that it is returning the correct values between 1 and +1, but when used as the trend it makes no difference.
Someone must know what we are doing wrong ( or is it that trend just does not work!).
Tuesday, July 29, 2008 2:53 AM 
I was having the same issue and what I noticed that Excel 2007 KPI trend value displayed in the formula bar was 0.5, but it showed the graphic for a value of 1. I changed the value in my KPI trend expression to return 0.4 and the problem worked fine. I'm guess there's an error in the display logic in Excel 2007.
Monday, September 22, 2008 1:40 PM