Waterfall Charts

Waterfall charts are part of the Business Charts introduced in Version 10.

Waterfall

For a standard waterfall, set the desired hierarchy and members on categories. The chart will then display each value as a waterfall accumulating in a total column/bar.

Right-click Options

Right-click on a waterfall column/bar to access options:

  • Total: set the category as a total, plotting from the category axis
  • Subtotal: plots from the last total
  • Custom subtotal count: if the category is set as a subtotal, you can set a custom count for how many previous categories it is the total for. If this is unset it subtotals from the previous total/subtotal (see example below)
  • Negate: plot the categories in the negative direction
  • Auto total after: add a total category after the selected one
  • Auto total title: change the category label of the auto title. The default is "Total"
Waterfall-Right-Click.png

Members can be configured as totals/subtotals/expenses automatically using Business Rules.

Custom Subtotal Count

In the example below, Operating Expenses is set as a subtotal on the Business Rules sheet. Because 'Standard Cost of Sales' and 'Variances' aren't child members of 'Operating Expenses', and the previous total/subtotal is for Net Sales, the subtotal lines don't line up correctly. We instead want the line to connect from the Labour Expenses category.

Waterfall-Custom-Subtotal-1.png

When we use the right-click option to set the custom subtotal count, in this case set it to 10, then the subtotal is displayed correctly.

Waterfall-Custom-Subtotal-2.png

Variance Waterfall

Variance waterfall charts display the categories contributing to the variance between totals. For example, in the chart below, the first half of 2018 had a value £103,246 greater than the first half of 2017, so this had a positive contribution to the change between FY 2017 and FY 2018.

Delta-Waterfall-Calculations.png

The values for the total bars are set in the Categories section of the taskpane. The variance between each one is then split up by whatever is on the 'Split Categories by'. This can either be the same hierarchy, or a new hierarchy can be placed in this area.

Variance-Waterfall-Taskpane.png

In the example below, FY 2016, FY 2017, and FY 2018 are the selected members in the Date hierarchy on Categories. In the 'Split Categories by' section is the Organisation hierarchy, with the five divisions as the selected members. This results in waterfall chart showing how each division contributed to the variance over each year.

Variance-Waterfall-Divisions.png

Delta Waterfall

Delta waterfalls display total variance rather than total values. They are setup in a similar way to Variance Charts, by setting two members to compare in the 'Compare by' section of the taskpane.

Contribution-To-Variances-Waterfall.png

Formatting

Formatting options are available in the formatting pane including the option to add a delta arrow.

WaterFallTotals.png