Losing your RAG

Variations of the Red-Amber-Green "RAG" performance rating system are used extensively. 

A common version is really a two rating (Red-Green) system i.e. where all performance is marked as either pass or fail. When over-used on a table of numbers (not least  with the common practice of colouring whole cells), this can produce a confusing and garish grid. 

A proper three-grade RAG system, including the central Amber, allows more sophisticated ratings to be made. But the Amber rating can be used in two very different ways:

• Fail but only just
• Pass but close to fail

Either way, including Amber in the pattern of fully coloured cells just adds further to the over-whelming blast of colour that assaults the reader's brain.

So why, so often,  do we end up with these types of reports and dashboards? 

The whole cell colour aspect  is probably mainly down to ease of production. It is much easier to produce this in say, Excel, than to use less over-whelming devices. 

The extensive use of Green is more curious. It probably indicates a service area where performance has regularly been below target and the people concerned are feeling a bit battered.  Lots of Red is demoralising so every opportunity is taken to proclaim the Green. The same motivation will probably lead to adopting the Amber "fail but only just" option. It removes more Red and it gives a feeling of "nearly there".

But before jumping into producing another one of these grids, the analyst should consider what the report or dashboard is actually trying to communicate. In the end, the main purpose has to be to highlight to managers when they need to make a decision or initiate action. 

This being so, what is the purpose of the Green? There are lots of months where the target was achieved. So what? What action will we take? Apart, possibly, from a momentary congratulation  - none. The Green is where we want to be. Colouring it in may give a sense of assurance, but it does not trigger any management action.

The Red rating is more obvious: we have not achieved the target and we need to do something.

What about Amber? A "fail but only just" statement is still a fail. We still need to do something. What we really need to know is where we met the target (Green) but only just. This is a situation where things may deteriorate and become Red. We may need to take action soon, or be pro-active and take action now, so we need to highlight this.

These considerations suggest that what would be best is a "RA" rating i.e. Red (fail) and Amber (pass but only just) system. Leave everything else un-highlighted on the grounds that there is no need to draw attention to it.

An example could look something like

where Red indicates that we missed the target and Amber indicates that we achieved the target but it was so close to the borderline that we may need to act, or at least keep a close eye on it.

This type of Red-Amber rating system can be developed further to make the Amber look more like a faint Red i.e to use a monochrome (Red-red) system in which tone conveys the meaning. Using this approach, together with some further adjustments to contrast, gives the following alternative:

This illustration uses exactly the same data as the grid above but is set out in a very different way. The idea is to adjust the contrast so that important information stands out and less important information melts into the background. 

Attention is drawn to the latest position rather than to the history of previous results. Indicator 4,  which missed the target is clearly highlighted by the Red marker. Two other indicators (5 and 7), which  only just hit the target, are also highlighted, but in a lighter tone.

The compact visual summary of the last six results then provides additional detail. Indicator 5 has generally been ok so there is probably no need to over-react. Indicator 7 has a history of missing the target. We should continue to monitor it closely.

In conclusion, for dashboards and reports, it may often make sense to abandon the customary RAG systems in favour of a Red-Amber (RA) or Red-red (Rr) as described here.

Finally, another practical reason to abandon the RAG is to help the 10% of males, and 1% of females,  who are colour blind.


References

3 Problems With Traditional KPI Traffic Lights by Stacey Barr. 20/05/2014

(link)

Dashboard Design for Real-Time Situation Awareness by Stephen Few. 2007 (link)


Excel: creating a 'Last Ten Results' indicator panel without using VBA by Edgar Bolton. B I Dashboard Design. 31/01/2016  (link)

Excel: creating a 'Last Ten Results' indicator panel without using VBA

The BBC website has many good examples of how to display information. The following  device is used to summarise a series of results. 

The BBC use it to display football results in a League Table (see link).  The concept is used more widely in Business intelligence dashboards. There are various approaches to creating this effect. This short article shows a simple way of constructing it using Excel without the need for any VBA coding (Excel skill level: Intermediate). The main idea behind the approach is to use an entire spreadsheet cell for each coloured bar. The colours are controlled by Conditional Formatting.

Step One
Set up a data area. Here, a block of ten successive results by the football team Leicester City F.C. is used. In the illustration, the data is set up on the same worksheet as the dashboard panel. In a real application, the data would probably be held separately. 

Step Two
Each set of bars requires three spreadsheet rows. The middle row is for the coloured bar; the rows either side are clear margins. Similarly,  additional columns are needed between each of the bar cells. Mark out the grid area  by filling each bar cell with a temporary grey background. Then link each bar cell to the corresponding results in the data area.

Step Three
Add Conditional Formatting to the bars. This can be done to the full range as a single block. Three rules will be needed, one for each colour:  In the example, "Win" is to display as Green; "Draw" as Grey and "Loss" as Red. Set the formats so that the font and the background colours are identical, within each rule.

Step Four
If desired, add a light background tint to the whole panel (and set the font colour exactly the same)



Step Five
Shrink the grid by setting column width and row height properties. In the example the following values are used: Bar row 3, margin rows 6, bar columns 1, interval columns 0.17. It may help to zoom to 200% while doing this.

Step Six
To add normal rows of text alongside the bars, merge the three rows in the area adjacent to the bars. This re-creates a more normal row height into which text can be added. When all the row and column adjustments have been made, switch off the grid-lines on the worksheet

It might seem fiddly to set up, but once one line has been created it can be cloned easily. Simply copy and then paste blocks of three rows at a time. 

Enhancements
An additional feature on the BBC website displays the details of each result when the cursor hovers over each coloured bar. A way of adding this type of functionality in Excel (using VBA) is set out in a supplementary article (see link) 



References

Excel: adding interactive drill down to a 'Last Ten Results' indicator panel  by Edgar Bolton. B I Dashboard Design. 31/01/2016  (link)

Excel: Using cell comments to provide "drill-down" details by Edgar Bolton
B I Dashboard Design. 05/08/2015   (link)

Excel: Using arrows to indicate direction of change in performance  by Edgar Bolton
B I Dashboard Design. 26/07/2015   (link)