Summary:
This article details how to create a Stack Chart that looks and acts like a Waterfall Chart for use in the Portal.
Waterfall charts give context to data by beginning each new value where the previous one left off. This shows how gains and losses relate to each other rather than showing them as stand-alone values. However, Waterfall Charts are not officially supported in the Cloud. Luckily the same effect can be manufactured artificially by setting up and formatting a Stack Chart to resemble a Waterfall Chart.
Method:
To get started, organize the data that the Waterfall Chart will be illustrating. Below is a typical setup for a standard Sales Flow for the year (the remaining months, followed by END, will be added in a later step).
Because Waterfall Charts are based on the ending point of the previous month, a PeriodCalc is needed to manipulate the figures. To do this, follow these steps:
- With Auto Parameters on, drag and drop Period End into the cell to left of January and create a group on the row.
- In the Layout Editor, drag and drop Period and assign it this PeriodCalc:
{PeriodCalc.GetPeriod(Period).Add(-11)} -
Drag and drop Monthly Amount under SALES FLOW for Jan.
Note: In our example, Monthly Amount is also divided by 1,000 and rounded. This is simply a design choice to make the numbers easier to read and is not necessary for the chart to function. This is all wrapped in an IFERROR statement to prevent the Excel error displaying while in Design Mode.
=IFERROR(ROUND(@OSRGet("GL_F_Trans","Value1")/1000,0),0). -
Copy and paste this row for the remaining months. Change the number in the PeriodCalc to start with -11 (Jan) and keep going up by 1 (-10, -9, -8, etc.) all the way to 0 (Dec).
-
Use the fill handle to populate the rest of the month labels. Type END below December.
- The Period End column can then be shaded grey and hidden.
Now that the basics are filled out, begin setting up the rest of the table. To do this, follow these steps:
- The SALES FLOW column will need a figure to act as its starting point for the year. In our example, this is 6,000. Type your starting point figure under SALES FLOW in the START row.
- Next, an IF statement is needed for the RISE column so that it only displays rises. Essentially, this statement should say, "If this month's sales flow is greater than 0, return the sales flow number. If it is less than 0, return 0." This statement will go under RISE in the START row.
=IF(G6>0, G6,0) - The FALL column should therefore display the inverse of the RISE column. Its IF statements should say, "If this month's sales flow greater than 0, return 0. But if it is less than or equal to 0, return the sales flow number flipped from negative to positive." This statement will go under FALL in START row.
=IF(G6<=0, -G6, 0) - Now to fill in the BASE column, reference the row above so that it's starting from the previous month’s ending point. This statement will say, “Last month’s base plus last month’s rise, minus this month’s fall equals our new base.” This statement will go under BASE in the January row.
=IFERROR(D6+F6-E7,0) - Pull the fill handle down to populate each column's formula for the rest of the months.
- Next, establish which Monthly Amounts to pull in. Make a group on the SALES FLOW column and assign it your Accounts. In the example shown, this is for accounts 40010:40040.
Now, to create the chart, select the data for everything except the Sales Flow column, click the Insert tab in the Ribbon, and choose the Stacked Column Chart.
Making this Stacked Chart take on the appearance of a Waterfall Chart all comes down to the formatting. The overall look and theme used are, of course, personal preference. But the essential component is to assign No Fill and No Border to the BASE. This will make the rises and falls look as if they are floating.
In our example, Data Labels have also been turned on.
Design Mode:
Rendered in the Portal:
Attachment
Attached is the example template used in this article with separate tabs for the steps along the way. Use this as a reference when creating your own Waterfall Chart.
Properties:
Cloud
Private Host
Report Designer