Overview
Application Scenario
A change waterfall chart can clearly reflect how a data record turns to be another after a series of increases and decreases.
For example, after various expenses are deducted from the monthly income, the final balance is obtained.
Implementation Method
The change waterfall chart is produced by stacked column charts, which is implemented as follows:
When the original data contains both positive and negative data, the negative data need to be converted into absolute values before being used in the column chart.
Except for the columns showing initial data and final data, other data needs to be displayed as absolute values in stacked placeholder data columns. The placeholder data needs to be hidden (with displayed data as absolute values) in charts.
To achieve this, you need to process data by formulas to obtain absolute values and placeholder data, bind cell data to charts, hide placeholder data under Special Effect > Condition Display in the chart, and change the colors of different categories.
Note:Example
Data Preparation
Choose File > New General Report in the upper left corner of the designer and create a built-in dataset named Income and Expense of the Current Month. Incomes are represented by positive integers and expenses are represented by negative integers.
Table Design
Table Style Setting
The following figure shows the designed table style.
Cells A2 and B2 come from the dataset, and cells C2, D2, and E2 are auxiliary data obtained using formulas. The following table describes the specific settings.
| Cell | Setting | Description |
|---|---|---|
A2/B2 | Directly drag the data column into the cell. | Set Expansion Direction to Vertical for the cell data. |
C2 | Insert the formula =ABS(B2). | Get the absolute value of cell B2's data as the final data displayed in the waterfall chart. |
D2 | Insert the formula =IF(&A2 = 1, B2, B2 + D2[A2:-1]). | This column lists the auxiliary data used to calculate the placeholder data in cell E2. This column actually lists the balance, namely the accumulated result in cell B2. &A2 represents the position number of cell A2 after expansion, and D2[A2:-1] represents the value of cell D2 corresponding to the value of the previous cell after cell A2 expansion. For details, refer to Layer-by-Layer Accumulation. |
E2 | Insert the formula =IF(B2 < 0, D2, D2 - B2). | Placeholder data: When the data in cell B2 is less than 0, keep the data in cell D2 unchanged. When the data in cell B2 is greater than 0, the data in cell D2 is D2 - B2. |
After the settings are completed, save the report and click Pagination Preview to preview the report, as shown in the following figure.
Condition Attribute Setting
According to the above preview effect, the placeholder data of the final balance is not 0. But the placeholder data of both the initial data and final data need to be 0. Therefore, you need to set a condition attribute for cell E2.
Select cell E2, click Condition Attribute on the right, add a condition attribute, set New Value to integer 0, add the formula A2="Final Balance", and click the Add button.
Preview the report again. The placeholder data of Final Balance is 0, as shown in the following figure.
Chart Setting
Chart Insert
Select a cell area, merge its cells, and insert a stacked column chart.
Data Binding
Select the chart, choose Cell Element > Data, and bind cell data.
Set Category to =A2, namely all the values expanded from cell A2.
Set Series to Placeholder and Value to =E2 for series 1.
Set Series to Absolute Value and Value to =C2 for series 2.
Chart Style Setting
(1) Chart Title
Choose Style > Title and set Text to Change Waterfall Chart.
(2) Legend Cancellation
Choose Style > Legend and deselect Legend Is Visible.
(3) Chart Label Setting
Choose Style > Label, select Use Label, select Value on the Common tab page, set Position to Outside, and set Overlap Adjustment to On.
(4) Condition Display Setting
Choose Special Effect > Condition Display, add three condition attributes, and double-click them to rename them.
Display Expense in Red: Add an attribute with Color set to red. Set Available Column to Category, Operator to Contain, and the value to Expense.
Display Income in Green: Add an attribute with Color set to green. Set Available Column to Category, Operator to Contain, and the value to Income.
Display Placeholder Data in Transparent Color: Add an attribute with Color set to Transparent. Set Available Column to Series Index, Operator to Equal to, and the value to Placeholder.
Table Hiding
After all settings are complete, select the rows where the table is located, right-click the row index, and hide the table.
Effect Preview
PC
Save the template and click Pagination Preview. The effect is shown in section "Application Scenario."
Mobile Terminal
Change waterfall charts are supported on both the mobile terminal and HTML5 end. The effect is shown in the following figure.
Template Download
For details, you can click to download Waterfall Chart.cpt.
