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)

Why month-based views are unsuitable for data which exhibits weekly patterns

One of the most commonly encountered formats for presenting data is a table of monthly totals,  often accompanied by a graph.

One reason for the extensive use of this approach is that it is very familiar and is practically self-perpetuating. It almost suggests itself.

Another reason is that it is a relatively easy calculation to produce month based counts using commonly available tools such as Excel, Access, SQL. Producing weekly aggregate numbers from raw data is a slightly higher level of difficulty. Similarly, producing a graph in Excel from a table of monthly totals is almost effortless. Producing an SPC (Statistical Process Control) type run chart is a little less easy.

A monthly review makes perfectly good sense. It is a reasonable time interval against which to assess a progress. Much less than a month and we risk over-reacting to short term fluctuations; much more than a month and we may not give ourselves enough time to act.

Where things can go wrong is where a good time interval for review gets confused with a good time interval for analysis.

Unfortunately there are many situations where a month-by-month view masks, rather than highlights, the real patterns in the data. People presented with a month by month graph in which the numbers go up and down will feel that they are seeing the real changes in activity. They may be reassured if the numbers go the 'right' way and panic if the numbers seem to go in the opposite direction.

The following illustration shows a series of monthly counts of events:

The monthly totals time series shows numbers going up and down suggesting a pattern of changing activity. 

Most readers will quickly spot that the pattern in the graph is nothing more than a representation of the number of days in each month. It has been built up from exactly 10 events each day throughout the entire period being studied. Nothing is changing at all.

The appearance of changing activity has been exaggerated by the default behaviour of Excel in truncating the vertical axis when creating graphs.

A way of compensating for this is to divide the total number of events in the month by the number of days in the month i.e. to calculate a daily rate. This is added on the illustration above. It shows that the monthly pattern of activity is actually static. 

For analysis, the rule should be that where the period being studied is divided up into unequal time periods (such as months), it is better to use a rate than to use a simple period total. It is surprising how often this simple rule is not followed.

In many situations, this compensating technique (translation into rates) may not be sufficient. The next illustration shows a series of monthly counts of events. The daily rate has been calculated. The rate goes up and down, suggesting change:

However, here again, the impression of a changing levels of activity is an illusion. 

The underlying pattern of activity is really static. It is a constant 28 events a week throughout the study period. For the purposes of illustration, the weekly pattern synthesised for the illustration has been strongly polarised (All Mondays have 7 events, Tuesdays 6 events, etc down to Sundays with 1 event). 

The spurious pattern in the illustration results from the interplay between the consistent but uneven distribution within each week in combination with the disposition of weeks across month end boundaries.

Another regularly used 'compensatory' technique is to show a monthly total alongside the equivalent month from the previous year:

Surely this approach is valid as, with the exception of some Februaries, we are comparing the activity in exactly equivalent time periods, side by side?

Unfortunately, again, the appearance of changing activity is an illusion. The graph is derived from exactly the same static pattern of 28 events per week as was used in the previous illustration.

If this is what static data looks like through this framework, imagine how hard it would be to make sense of any real patterns of change.

In areas where there are strong weekly patterns, such as healthcare, viewing data through the distortion of a monthly framework makes it very difficult to see what is really going on.

So while monthly views work well for appropriate uses, such as showing cumulative income year-to-date, they will often be a poor choice for displaying and understanding patterns of event activity. 

Unlike the month, the week is a more 'natural' time period which aligns well with patterns of behaviour. It is worth the extra effort of aggregating event data by week and looking at it in weekly run charts. The SPC method can be used to assess whether the variation seen reflects any real changes.

At the very least, a preliminary analysis will be needed to assess the strength of weekly patterns in the data before a decision can be made on how much 'noise' this is likely to create in a monthly time series .

Another technique used in reports and dashboards is to divide monthly totals of events by the number of 'working days' in a month. This can go some way towards making comparisons better. But it still does not compensate fully for a strong weekly pattern. 

Also, it would be necessary to review each measure being reported on and assign an individual number of working days to it. This can vary from measure to measure. Some events would be expected to occur on weekends and bank holidays. Some services may operate for less than five days a week. It cannot be assumed that using the number of 'working days' worked by the analyst in the month is the correct denominator in every, or even any, case.