This post is a first in series aimed at beginners. Mirroring and expanding the content we share at Data Girls workshops this series attempts to improve your practice, or just build the fundamentals. Let us know in comments below how do you like and what would you like to see next!
Aggregation in Dashboards
Aggregation is the key to any storytelling in dashboards. Business always works on numbers and not the details beneath.
In the era where data is moving at such a fast pace, we need more and more dashboards which give information just with the glance of an eye.
It’s always said that number speaks more than words and that is what aggregation in a dashboard is all about.
But what if the dashboard talks about too many numbers with roll up and roll down displaying information from top level to detailed level and this is when pictures speak more than numbers
This is the point when we do another level of aggregation on dashboards. You can call them aggregation on aggregation. This is how we make colour dashboards where numbers are replaced by colours.
Colour dashboards are mostly used for Strategic and Operational Dashboards.
Strategic dashboards – Dashboards provide managers and executives at all levels of the organization the information they need understand the health of the organization and help identify potential opportunities for expansion and improvement.
Operational dashboards are used to monitor real-time operations and alert the users to deviations for the norm. It contains less information and makes an alert when something deviates from the acceptable standards
Interestingly these dashboards have to represent anything in terms of an indicator OR colours where the calculation behind is very complex.
As too many cooks spoil the meal, we have to be very careful while defining the colour bands and range as the colours should not increase the complexity of the presentation.
To start with let’s start with RAG dashboards. RAG stands for Red, Amber and Green.
In these dashboards, all the calculations are represented in 3 categories.
Green – Much within threshold value
Amber – Within limits but moving closer to threshold value
Red – way above threshold value
The methodology behind these dashboards is aggregation methodology. There are various methodologies but let’s look at the most common ones –
The first approach is to calculate RAG score at each and every level –
To understand this in more detail, let’s dig down on the aggregation methodology
First set the objective of the aggregation methodology i.e. is to calculate a measure that:
- Reflects the relative importance of components – by using Weighting
- Reflects the RAG status of each component – answering “How many reds, ambers and greens.
- Reflects the state of a component within the RAG status – answering “How red is a red”
|1||Establish the RAG of a component||RAG thresholds||Thresholds may evolve over time|
|2||Capture realistic values and increase comparative value||Adjustments to RAG thresholds||Adjustments are ad hoc and may change over time|
|3||Capture the state of a component within the RAG (e.g. how far in the red threshold)||Calculate RAG status percentage|
|4||Translate the RAG status into a score that reflects the status||Calculate RAG score||The bands stand equally at 33.33 points each but this may change and recalibrate over time (e.g. 50-30-20)|
|5||Assign relative values to each component and factor them in||Weightings||Weightings will change over time; number of components will change over time|
The second approach is traditional bottom up where the most granular level calculation is computed and bought to a level where it can be categorized as Red, Amber or Green (RAG). This is rolled up to next level depending on what the child RAG status is and so on.
|1||Get the RAG calculated for each individual child (The lowest level)||Get RAG for every child where each child has different Thresholds||Thresholds may evolve over time||If child value is 30 and Threshold says 0-50 is RED, 51-70 is AMBER and 71-100 is Green then Child RAG is RED|
|2||Get RAG for all the child’s, corresponding to parent||Get the Threshold for the corresponding parent||Each parent can have same or different RAG calculation||If 30% of child are RED and 70% Child are Amber and 0% child are Green then AMBER and so on.
Depending on the parent RAG calculation definition, determine the Parent RAG
|3||Capture the RAG of Parent and move to next level up in hierarchy||Get the RAGs of one level below||Similarly calculate RAG for this parent||This can be calculated same like step 2 o can have a different definition all together|
There are numerous methodologies and techniques defined for aggregation calculations and what we discussed above are just the most common ones used in industry.
Definition and implementation of these techniques vary vastly and once you become comfortable and confident understanding these methods, they are really interesting and challenging to work on.