Advanced Thresholds in SAP Analytics Cloud
Author – Mark Judson, Senior Business Intelligence Consultant, NTT DATA Business Solutions UK
SAP Analytics Cloud (SAC) is a Software-as-a-Service (SaaS) solution combining Business Intelligence, Planning, Predictive and augmented analysis capabilities. I have been developing SAC solutions for the last 2 years.
Within SAP Analytics Cloud, Thresholds are used to define “conditional formatting”, whereby we can apply colour to our analytics based on certain conditions within the data. A simple example of this is Red/Amber/Green (RAG) statuses. In SAC we can configure Thresholds for Measures, comparing them to other Measures or to Numeric Ranges.
On a recent project, we were given the challenge of setting up KPI Thresholds for more complicated sets of criteria. The customer wanted to look back at performance across a number of weeks, and to also take into account current performance against budget, in order to derive a “call-to-action”. The call-to-action would be a coloured indicator, one of Green, Amber, Red or Super-Red.
Taking one of the customer’s KPIs, Capacity %, the call-to-action requirement was as follows:
- Green – Capacity UP/EQUAL in 1 of the last 2 weeks AND currently better than Budget
- Red – Capacity DOWN in each of the last 3 weeks and currently worse than Budget
- Super Red – Capacity DOWN in each of the last 4 weeks and currently worse than Budget
- Amber – All other combinations
The main challenge here was that we had a number of Measures that we wanted to evaluate, using some IF/THEN/ELSE logic. The following Restricted Measures were defined within the Story:
The first part of the solution required the creation of a Calculated Measure to evaluate the Threshold logic and then to assign a numeric value as the outcome. This numeric outcome would then be used later to setup the Numeric Ranges within the Threshold. The values were assigned as:
- 3 – Green
- 2 – Amber
- 1 – Red
- 0 – Super Red
We then created the Calculated Measure – “RAG – Capacity %” – with the following formula, assigning a 3, 2, 1 or 0 as appropriate:
Next, we defined a Threshold on the Measure “RAG – Capacity %”and setup the Ranges as follows:
Configuring the Widget
Finally, we assigned “RAG – Capacity %” to the “Colour” in Builder for our associated Numeric Point Chart, and the KPI was set to the appropriate colour – Amber in the example below:
Additional Requirement – Presentation
Although we were able to show the KPI with the appropriate Threshold colour, the customer also asked if we could enhance this, to create a stronger call-to-action (flashing borders were mentioned!). Following some thoughts on what was available in SAC and how we could utilise other components, we came up with a solution whereby we would place a coloured right-hand border on the Numeric Point Chart.
To create the border we first created a new 100% Stacked Bar Chart for the “CW Capacity %” Measure, and with the Colour set to “RAG – Capacity %”, as shown below:
Via the Show/Hide menu, we then hid everything until we were just left with a coloured bar as below:
Finally, we resized the Bar Chart and moved it into position next to the Numeric Point Chart.
NOTE, this solution works fine using Canvas Pages where the Story is built for WYSIWYG (as this one was), but not for Responsive/Mobile Pages which, due to their very nature, don’t really handle overlays or clever use of widgets in the same way.
For more information on SAC and other topics in Analytics, you may be interested in registering for #itelliFest2020 On Demand.