Academy

A waterfall chart, or bridge chart, a special visualization often used in financial reports. It is a specific data visualization that shows how a specific total is transformed to another value by showing the cumulative result of changes between these.

The waterfall chart is very useful to visualize financial data:

• to provide an explanation for the deviations from a benchmark value by zooming into the underlying details (for example: months, business units, products, … etc). I call this type the “variance break-down waterfall chart”;
• to show the progressive buildup of a profit & loss statement (or parts of it). I call this type the “P&L break-down waterfall chart”.

Both types of waterfall charts have each their specific application in financial reports and dashboards. When used with icCube’s dynamic filtering and drill-by options, these visualizations will provide a powerful tool for the finance professional to analyze performance and track profitability.

This blog post will outline the steps to create the first type of waterfall chart in icCube, using the standard serial chart: the variance break-down waterfall chart. I will document the steps to create the second type later this month.

If you want an interactive demo in icCube, to see the “waterfall – variance break down” live, click here.

This will be the end result if you have followed the five steps of this blog post:

the data set for the variance break-down chart

Below the data set you need to create the variance break-down chart. For illustration purposes , the totals for actuals and budget have been included.

Both break-downs explain the variation between cumulative Actuals and Budget on May 2018. I use the following definitions in this data set:

• ist – the actual value (“ist” is German for “is”);
• soll – the benchmark or reference value, in this case the budgeted value (“soll” is German for “to be”)

I have kept the required data set as simple as possible, so it can be easily reused in other visualizations (variance chart or just a plain column chart).

details of the steps to create this visualization

The visualization will have the following characteristics”

• chart type: serial chart (amCharts) in icCube;
• use of the “open” and “close” data fields to draw the columns;
• conditional coloring of the bars depending on the value;
• conditional text in the balloons;
• use of custom numeric formatting;
• use of javascript to calculate the right data points;
• use of MDX to add an additional start and end row;
• allow the correct display of expense accounts;
• the visualization is flexible with regard to the number of data rows.

(Expense accounts are accounts which value will be subtracted from the profit. An excess of the actual value compared to the benchmark is considered to be negative and should be marked as such. For example: you have spent too much money on Marketing.)

step 1 – the data manipulations in MDX

As you can see from the waterfall chart image at the beginning of this post, just the break-down figures are not enough, I also need the start value (the total Benchmark) and the end value (the total Actuals):

To add these additional rows, I use the following MDX:

[icCubeMdx]

WITH
// set the rows definition    
SET [bridge] as descendants([Business Unit].[BU],,leaves)    
MEMBER [Business Unit].[BU].[start] as 0, caption = "Benchmark"
MEMBER [Business Unit].[BU].[end] as 0, caption = "Actuals"    

// set ist and soll
MEMBER [Measures].[ist]  AS ([measures].[amount],[scenario].[actuals])
MEMBER [Measures].[soll] AS 
    iif([Time].[Time].currentmember.key <> null and [Time].[Time].currentmember.key <= currentmonth().key
        ,([measures].[amount],@{selBenchmark:asMdx})
        ,null
    )
// is ist an expense type (i.e. excess to soll is negative, shortage is positve)?
MEMBER [Measures].[@et] AS iif([ist] = null,null,[Account].[Account].currentmember.properties('@et'))

// do not change
SET [rows] as [start] + [bridge] + [end]

SELECT
// Measures
{[Measures].[ist],[Measures].[soll], [Measures].[@et]} ON 0,
// Rows
NON EMPTY @{selAccount} * [rows] ON 1
FROM  [Finance]
WHERE {@{selView}} * @{selYear} 
CELL PROPERTIES STYLE, CLASSNAME, VALUE, FORMATTED_VALUE, FORMAT_STRING

[/icCubeMdx]

A short explanation:

• parameters:
• @{selBenchmark} – value for the benchmark. One of Budget, Prior Year, Forecast.
• @{selAccount} – selected account
• @{selView} – selected view on the data. One of: Periodic (loaded data), YTD (cumulative)
• @{selYear} – selected period
• functions:
• currentMonth() – date of the last actual month loaded
• the row definition is defined in the data set [bridge];
• two calculated members [start] and [end] are added to make up for the [rows];
• the measure [ist] is calculated using the [amount] at [Scenario].[Actuals];
• the measure [soll] is calculated using the [amount] at @{selBenchmark}. Note that you do not want to show the future periods for the soll, therefore check if the period exceeds the currentMonth();
• the measure [@et] retrieves the property “@et” (= Expense Type) for the selected account.

To create the chart do the following:

1. create a new Serial chart;
2. add the MDX to the “MDX” tab in the “Data” section (change to your data model)

Here is the result:

step 2 – filling the data series

The chart from step 1 looks nowhere near the bridge chart required. We need amChart to calculate the total start and end value, including the variances between the ist and the soll as indicated by the following figure:

To calculate this data do the following:

3. In the “Data Render” tab, add the following “value” function to the “value” field.

In the javascript, the JS expressions as documented here are used.

Value + Edit:

// close field
// close field = total_soll + cum_ist - cum_soll
var label = context.rowLabel(context.getRowIndex());
var total_soll = context.sumCol(0, 'soll');

if (context.getRowIndex() === 0) {
    return total_soll;
} else if (context.getRowIndex()  == context.rowsCount -1) {
	return context.sumCol(0, 'ist');
} else {
    var cum_soll = context.cumulativeCol(0, 'soll');
    var cum_ist = context.cumulativeCol(0, 'ist');
	return (cum_ist - cum_soll) + total_soll;
}

4. In the “Data Render” tab, click on the radar icon in the “column graph”, click “data fields” and add the following javascript code to the “open field function”.

open field function:

// open field = close field - (current_ist - current_soll)
// close field = total_soll + cum_ist - cum_soll
var total_soll = context.sumCol(0, 'soll');

if (context.getRowIndex() === 0) {
    return 0;
} else if (context.getRowIndex() === context.rowsCount-1) {
    return 0;
} else {
    var cum_soll = context.cumulativeCol(0, 'soll');
    var cum_ist = context.cumulativeCol(0, 'ist');
    var ist = context.getValue('ist');
    var soll = context.getValue('soll');
	return (cum_ist - cum_soll) + total_soll - (ist - soll);;
}

The result of step 2:

Step 3 – adding conditional coloring

Now, we want to color the first and end value grey. The intermediate values follow the following logic:

5. To add the conditional coloring, click on the (2) “Color Mode” “Palette for Legend” button in (1) the left panel of the “column graph” and add (4) the following java script code to the section (3) “<> Expression”:

the javascript code to add:

var label = context.rowLabel(context.getRowIndex());
if (context.getRowIndex() === 0) {
    return "#858585"; // grey
} else if (context.getRowIndex() === context.rowsCount-1) {
	return "#858585"; // grey
} else {
	var ist = context.getValue('ist');
	var soll = context.getValue('soll');
    var expenditure_type = context.getValue('@et');
	var variance  = ist - soll;
    if ((variance > 0 && expenditure_type == 'E') || (variance < 0 && expenditure_type != 'E') ) {
        return "#D53E4F"; // red
    } else {
        return "#66C2A5"; // green
	}
}

result of step 3:

step 4 – adding the labels

Next is to add the labels with the values, neatly formatted according to its size. I am using a custom javascript function for this that formats the values in a short and readable format:

function formatNumber(value, precision=1) {
    var factor = Math.pow(10, precision);
    var thousand = 1000;
    var million = 1000000;
    var billion = 1000000000;
    var trillion = 1000000000000;
    if (Math.abs(value) < thousand) {
        return String(Math.round(value*factor) / factor);   
    }
    
    if (Math.abs(value) >= thousand && Math.abs(value) <= million) {
         return  Math.round(value/thousand * factor) / factor + 'k';  
    }
    
    if (Math.abs(value) >= million && Math.abs(value) <= billion) {
        return  Math.round(value/million * factor) / factor + 'M';   
    }
    
    if (Math.abs(value) >= billion && Math.abs(value) <= trillion) {
        return  Math.round(value/billion * factor) / factor + 'B';   
    }
    
    else {
        return  Math.round(value/trillion * factor) / factor + 'T';   
    }
}                                                                                                                                                                                                                            

6. add the formatNumber function to the dashboard Configuration > Report Javascript, or alternatively add it to the ic3report-local.js as described here;
7. open the (2) “Bullets and Labels” section for the (1) “Column Graph” and (3) add an “x” tot he “Label Text”. Next (4) add the code below to the “ Label Function”:

the javascript code to add:

/**
 * Return label text
 */
function(graphDataItem, formattedText) {
    return formatNumber(graphDataItem.values.value-graphDataItem.values.open);
}

This gives the following result:

final step – add varying balloon tekst

To help your audience to understand the chart, I want to display the balloon text according to the following information:

This is how to do that.

8. add the following javascript code to the “Column Chart” and “Balloon Text” field in the left panel:

// call to custom JS function formatNumber
var p = 1; //precision
if (context.getRowIndex() === 0) {
    return "total @{selBenchmark:caption}: "+formatNumber(context.sumCol(0,'soll'),p);
} else if (context.getRowIndex()  == context.rowsCount-1) {
    return "total Actuals: "+formatNumber(context.sumCol(0,'ist'),p);
} else {
    var ist = context.getValue('ist');
    var soll = context.getValue('soll');
    var expenditure_type = context.getValue('@et');
    var variance  = ist - soll;
    if ((variance > 0 && expenditure_type == 'E') || (variance < 0 && expenditure_type != 'E') ) {
        var fVar = "<font color='#D53E4F'>"+formatNumber((variance),p)+"</font>";
    } else {
        var fVar = formatNumber((variance),p);
    }
    return "variance: " + fVar + "<br>Actuals: " + formatNumber(ist,p) + "<br>@{selBenchmark:caption}: "+ formatNumber(soll,p);
}	

The final result is now:

Conclusion

These five steps have transformed the soll and ist data set into a waterfall chart that breaks down the total variance between a benchmark value and the actuals to its details. In this particular example we did this for the business units, but something similar can be easily done for the months. The only thing to be changed are the first three lines in the MDX statement.

Having the waterfall chart defined in the standard serial chart gives us additional fine-tuning options that are available by default, such as adding a cursor, adding a scroll bar, turning the chart to a bar chart, etcetera.

For example:

I hope you enjoyed making this visualization. Please share with me your results, my contact details are below.

By Arthur van den Berg