Change Waterfall Chart

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:

Formulas used in this document are applicable when initial data and final data are positive numbers. In other situations, you may need to modify formulas yourself.

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.

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.

For details, you can click to download Waterfall Chart.cpt.