Inverted calculated thresholds – negative numbers?

Updated on July 27, 2015 in Scorecards
3 on July 27, 2016

I’m trying to set a metric threshold based on another un-scored control metric (Actuals based on Forecast) and so have used the ‘calculated’ function when defining a goal/red flag metric. The calculation is very simple. I want the goal to be the forecast value and the red flag to be 90% of the forecast value so the calculations I have are:

Red Flag: M(1310) * 0.9
Goal: M(1310) * 1

Where M(1310) is the Forecast value set up in advance.

The problem is, the RAG has inverted so that the red is on the top of the chart and the green at the bottom.  i think it might have something to do with negative numbers as the first couple of forecast numbers are -1345 and -1253.  has anyone seen this before?

  • Liked by
2 on July 27, 2016

Are all of the forecast numbers negative?  Using calculations in QuickScore is similar to a spreadsheet, it will only do what you tell it to do.  if you try your calculations in a spreadsheet mock up first, it’s a good way to test the results quickly.

on July 27, 2016

No, most of the forecast numbers are positive.  It’s a typical forecast, low at the beginning of the year and high towards the end.  Why, does this make a difference?

on July 27, 2016

Yes, it does.  Looking at what you have above QuickScore will do the first calculation and provide a HIGH negative number.  The second will result in a LOW negative number.  By default, QuickScore will assign Red to high.  As the graph can’t switch from Red high to Green high (it doesn’t make sense), you will be stuck with red high.

The solution is to apply an IF test that forces Green to be high, using the same M number you have above it will look like this:

Red Flag: if ( M(1310) < 0, M(1310) * 1, M(1310) * 0.9 )
Goal: if ( M(1310) < 0, M(1310) * 0.9, M(1310) * 1 )

Show more replies
  • Liked by