The above chart is attempting to visualise the evolution of the selected account (the Operating Costs in this case) in Dec from what it was in Jan across all the entities. The extreme left and right bars (in blue) are the anchors showing the starting and ending position of the Operating Costs for Total Australia while the bars in between (the red and green bars) represent the movements (up or down) for each child entity of Australia; up represented in green and red otherwise.
You might be wondering what's the big fuss about this? It looks just like the normal column chart that we can already produce in PBCS. You're right. It is a column chart but with a twist!
The twist...
You see, unlike the column chart we are used to, we need to have the movement bars be plotted relative to the position of its previous bar. For instance, the bar for Victoria (second one from left) needs to begin from where the previous bar ends (in this case at $226M for Australia in Dec FY18). The third bar (South Australia) will need to begin where Victoria has left off and so on and so forth. How can we do that with the a bar chart? A normal column chart will begin plotting from $0 of the Y-Axis. So how can we trick the bar chart to do what we want? The secret lies in manipulating the empty bars below the red and the green bars. Notice that I have set the background to a light grey? The reason is not because of aesthetics but rather we have bars coloured in the same colour as the background to give you the illusion that the movement bars are plotted relative to their immediate left bar. There are hidden bars plotted below the red and green bars and they all start from 0. Gotcha! The screenshot below shows you the hidden bars (which I have set to black here to reveal them).
You might also be wondering why didn't I set the background and the hidden bars to transparent? Well, I did, but unfortunately, they turn to black the next time I accessed the dashboard. A little bug for Oracle to solve. Now let's dive into the details of this solution, shall we?
Nuts and Bolts
This example requires users to make the following selections to perform the comparison:- Comparison Scenario 1 & 2
- Comparison Version 1 & 2
- Comparison Year 1 & 2
- Comparison Period 1 & 2
- Parent Entity
- Account
The chart will then make comparison of the selected account across the children of the selected entity between Comparison 1 and Comparison 2 data sets. You could vary this to compare across other dimensions that you may have (e.g. Cost Centres, Department, etc).
Remember I mentioned about manipulating the blank bars earlier? Well, we have other elements that we need to handle too and here's the list of what's required:
- Anchor - the starting and ending values for the chart.
- Delta - the difference between Comparison 1 and 2.
- Increase - contains the absolute positive delta value.
- Decrease - contains the absolute negative delta value.
- Blank - the value of this element is dependent on the value in the previous bar (we will discuss the logic in more detail later in this blog).
These elements need to be created in a separate dimension and they are in the Measures dimension in this example.
A business rule is set to launch each time the dashboard is loaded to ensure the latest numbers are shown. Let's examine the rule.
The Heart of the Solution...
In the nutshell, the rule covers the following processes:
- Step 1 - Populate all relevant measures and accounts for Comparison 1 Scenario, Version, Year, Period.
- Step 2 - Populate all relevant measures and accounts for Comparison 2 Scenario, Version, Year, Period.
- Step 3 - Calculate the Delta, Increase, Decrease, and Blanks for the Waterfall Chart.
Step 1
Step 1 - Comparison 1
- Step 1a - Clear Previously Populated Values
- Step 1b - Populate Anchor Measure (Parent Entity Only)
- Step 1c - Transfer the Income Statement Values to the Waterfall Accounts (Child Entity)
We start off with the clearing of data from the waterfall related measures to ensure that all previously populated numbers are cleared. This is followed by populating the anchor measure with the selected parent entity value for each of the Income Statement accounts for comparison 1 data set (Scenario, Version, Year, and Period). We will finally transfer the Income Statement account values to their respective Waterfall accounts. Such accounts are required should you have other Waterfall Charts to prevent on wterfall chart from overriding data of another. This example demonstrates the waterfall chart for entities, so I've created a set of waterfall accounts for entity, for instance:
- NPAT
- Revenue
- Operating Costs
- Non-Operating Costs
- etc..
You'd create another set of waterfall accounts for another chart, e.g. another set for Cost Centre.
Step 2
Step 2 - Comparison 2
- Step 2a - Clear Previously Populated Values
- Step 2b - Populate Anchor Measure (Parent Entity Only)
- Step 2c - Transfer the Income Statement Values to the Waterfall Accounts (Child Entity)
They are identical to the processes described in step 1. The difference is in step 2, we are dealing with the Comparison 2 data set.
Step 3
Step 3 - Calculate the Measures for the Waterfall Chart
- Step 3a - Calculate the Delta
- Step 3b - Determine the Increase/Decrease
- Step 3c - Create Blocks
- Step 3d - Determine the Blanks
This step deals with the logic to produce the movement bars. We begin with calculating the difference between Comparison data set 1 and set 2 for all the children of the selected entity. The delta is then evaluated to assign its abosolute value (without signs) to either the increase or the decrease measures. Once done, we proceed to create the necesary blocks before we determine the values for the blank bars. Here, we need to first determine the anchor value if it is a positive or a negative. There is a difference in the formula to calculate the blank for a positive anchor and a negative one. Let's review the logic for each one:
Positive Anchor
Assign the starting anchor value to the blank measure if it is the first child of the selected entity. So, in this example, Victoria (which is the first child in the Australia entity hierarchy) will have the anchor value minus the decrease of the first child entity assigned to its blank measure.
All subsequent entities will have the following formula to calculate the blank measure:
Negative Anchor
The logic for the first child of the selected entity with a negative anchor is shown below. Differences are highlighted:
The blank calculations for the remainder of the child entities, again with differences highlighted:
Forms and Dashboard
To produce the chart in a Dashboard, we'd need 2 forms to be created, one each for the grid and the other to plot the Waterfall Chart. The form to show the grid has the IChildren of the selected entity across the columns with 3 measures in the rows, namely:
- Anchor
- Increase
- Decrease
The form for the chart on the other hand requires the same settings for the columns but the rows contains the following measures:
- Anchor
- Blank
- Increase
- Decrease
For the dashboard, create one and include the above 2 forms in the dashboad with the latter form (for chart) being set with a Chart Type of Column. Remember to change the colour for the data series:
- Series 1 (Anchor) - Blue
- Series 2 (Blank) - Light Grey
- Series 3 (Increase) - Green
- Series 4 (Decrease) - Red
And please remember to set the background colour for the chart to be Light Grey as well so that the Blank bar appears hidden.
There you go. The complete code line to produce the Waterfall Chart.
Before we go, some parting remarks...
Did you realise that this example covers only positive starting and ending numbers (anchors)? This works perfectly when both numbers are positive or both are negative. Additional logic and measures are required to address other data conditions such as:
- Starting anchor is positive and ending anchor is negative or vice versa.
- The movements moves from positive to negative or vice versa.
To make this post short and precise, I've decided to start off with the easy stuff and then cover the complex one in another post. So, stay tuned for my next post where I'll discuss the logic to cover the above conditions. Till then, stay safe and stay home! #Covid-19
