How do I build a waterfall chart?

A waterfall chart displays an initial value followed by a series of intermediate values to demonstrate how the starting value increased and decreased over time. Each gain and loss displays as a floating column, with the final column representing the final total.

Klips doesn’t include a built-in waterfall chart component. This article describes how to build one using the Stacked Bar Chart component.

When should I use a waterfall chart?

This chart type is often used by finance and sales teams to help them understand the causes behind value changes over a set period of time.

How do I read a waterfall chart?

Waterfall charts are read from left to right. The first bar is fixed to the horizontal x-axis and represents the starting value. The floating intermediary bars are sorted chronologically or sequentially to show a flow of events. Positive changes, usually coloured green, start at the top of the previous bar (the baseline) and point up. Negative changes, usually coloured red, start at the bottom of the previous bar (the baseline) and point down. The last bar on the right is fixed to the bottom axis and shows the combined total of positive and negative changes.

How do I build a waterfall chart in Klips?

This article shows you how to build waterfall charts in Klips using two examples:

• Example one: Tracking employee headcount
• Example two: Tracking financial data

Example one: Tracking employee headcount

This example walks you through creating a waterfall chart in Klips that shows employee headcount changes over time.

We’ll create this waterfall chart by configuring a stacked bar chart that includes these three data series:

• Baseline: This invisible spacer acts as a starting line (a baseline) for each floating bar. The colour for this series is set to white to hide it from view.
• Journey: These bars represent the changes (positive or negative values) that "float" in mid-air.
• Start & End: This series handles the full-height, fixed bars that display at the beginning and end of the chart.

In our scenario, a team started with 20 employees, had headcount increases and decreases, and ended the period with the same number of employees.

These are the events that took place:

• Start: 20 employees
• Loss: 15 people quit
• Gain: 10 new hires
• Gain: 5 new employees transferred from a different team
• End: 20 employees

If you visualize this data with a bar chart, it would seem like nothing changed. With a waterfall chart, you can see the whole picture.

Our example uses this data (that you can download here):

When you’re finished, your Klip should look like this:

To build the employee headcount waterfall chart example:

1. Get the example data source: Download the data source and save it on your desktop.
2. Add a custom Klip: From a dashboard, click + Add Klip > Build a custom Klip, or, from the Klip Library, click +New custom Klip.
3. Set up a stacked bar chart that includes 3 series:
   • In the component tree, click Klip and in the Properties tab, at Klip Title, enter Employee Headcount (waterfall example).
   • Click and drag the Bar/Line Chart component into the Klip preview workspace.
   • With the Bar/Line Chart component selected:
     • At Legend, deselect the Show Legend checkbox.
     • At Stack Bars, select Stack all bars.
     • In the upper-right corner of the preview workspace, click the +/- button to show the controls palette and add 2 series to the chart.
       
4. Add the data source: Select Klip in the component tree. Open the Data tab. Click + Add Data Source > Create a New Data Source > File upload, and select the example data source you downloaded (don’t model it). Click Get data > Continue > Save.
5. Configure the Axes and Series:
   • Select the X Axis. Open the Data tab and select data using the following formula structure:
     ARRAY("START,ATTRITION,HIRING,TRANSFERS,END")
   • Select the Y Axis. At Grid Line, deselect Show grid lines.
   • Select the first series, click its 3-dot menu > Rename and name it Baseline. Then, open the Data tab and select data using the following formula structure:
     LOOKUP(
     &X Axis: Untitled,
     ARRAY("ATTRITION,HIRING,TRANSFERS"),
     ARRAY(@Sheet1,B2-ABS(@Sheet1,B3),
     @Sheet1,B2-ABS(@Sheet1,B3),
     @Sheet1,B2-ABS(@Sheet1,B3)+@Sheet1,B4))
   • Select the second series, click its 3-dot menu > Rename and name it Journey. Then, open the Data tab and select data using the following formula structure:
     LOOKUP(
     &X Axis: Untitled,
     ARRAY("ATTRITION,HIRING,TRANSFERS,END"),
     ARRAY(ABS(@Sheet1,B3),@Sheet1,B4,@Sheet1,B5))
   • Select the third series, click its 3-dot menu > Rename and name it Start & End. Then, open the Data tab and select data using the following formula structure:
     LOOKUP(
     &X Axis: Untitled,
     ARRAY("START,END"),
     ARRAY(@Sheet1,B2,@Sheet1,B6))
6. Set the series colours:
   • Select the Baseline series, At Series Color, click Override the default color and select white.
   • Select the Journey series. Note that the colour for columns in this series varies depending on whether they show a positive (green) or negative (red) change.
     Open the Indicators tab, and enter the following:
     • If X Axis: Untitled contains ATTRITION then change color (select red)
     • If Series: Journey then change color (select green)
       
7. Select the Start & End series. At Series Color, click Override the default color and select a shade of blue.
8. Click Save and Exit or click its drop-down arrow to view other options.

Example two: Tracking financial data

This example walks you through creating a waterfall chart in Klips that shows changes to financial data over time.

We’ll create this waterfall chart by configuring a stacked bar chart with two data series and a hidden data component and use this data source (that you can download here):

When you’re finished, your Klip should look like this:

To build the financials waterfall chart example:

1. Get the example data source: Download the data source and save it on your desktop.
2. Add a custom Klip: From a dashboard, click + Add Klip > Build a custom Klip, or, from the Klip Library, click +New custom Klip.
3. Set up a stacked bar chart that includes 2 series and a Hidden Data component:
   • In the component tree, select Klip and in the Properties tab, at Klip Title, enter Financials (waterfall example).
   • Click and drag the Bar/Line Chart component into the Klip preview workspace.
   • With the Bar/Line Chart component selected:
     • At Legend, deselect the Show legend checkbox.
     • At Stack Bars, select Stack all bars.
     • In the upper-right corner of the preview workspace, click the + / - button to show the controls palette. Add one series, then add one hidden data component.
       
4. Add the data source: Select Klip in the component tree. Open the Data tab. Click + Add Data Source > Create a New Data Source > File upload, and select the example data source you downloaded (don’t model it). Click Get data > Continue > Save.
5. Configure the Axes, Series, and Data component:
   • Select the X Axis:
     • Open the Data tab and select column A from the data source. This enters @data,A:A into the formula bar.
     • Click its 3-dot menu and select Filter > Member > Exclude > Step / Category > Filter.
   • Select the Y Axis. At Grid Line, deselect Show grid lines.
   • Select the first series (it's like the Baseline series from example one). Then, select data using the following formula structure:
     LOOKUP(&X Axis: Untitled,
     ARRAY("Gross Revenue,Returns & Refunds,Net Revenue,Cost of Goods Sold (COGS),Gross Profit,Marketing & Sales,Payroll & Admin,Tax Rebate,Net Income"),
     ARRAY(0,
     @data,B2+@data,B3,
     0,
     @data,B4+@data,B5,
     0,
     @data,B6+@data,B7,
     @data,B6+@data,B7+@data,B8,
     @data,B6+@data,B7+@data,B8,
     @data,B10
     ))
   • Select the second series (it's like the Journey and Start & End series from example one). Then, select data using the following formula structure:
     LOOKUP(&X Axis: Untitled,
     ARRAY("Gross Revenue,Returns & Refunds,Net Revenue,Cost of Goods Sold (COGS),Gross Profit,Marketing & Sales,Payroll & Admin,Tax Rebate,Net Income"),
     ARRAY(@data,B2,
     ABS(@data,B3),
     @data,B4,
     ABS(@data,B5),
     @data,B6,
     ABS(@data,B7),
     ABS(@data,B8),
     @data,B9,
     @data,B10
     ))
   • Select the Data component.
     • Click its 3-dot menu > Rename and name it Indicator.
     • Open the Data tab and select column C from the data source. This enters @data,C:C into the formula bar.
6. Set the series and data component colours:
   • Select the first series. Open the Properties tab. At Series Color, click Override the default color and select white.
   • Select the second series.
     Note: Column colours here depend on whether they display totals or subtotals (blue) or whether their values show a “negative” (red) or “positive” (green) change.
     For the total and subtotal columns: Click the Properties tab. At Series Color, click Override the default color and select a shade of blue.
     For the negative/positive columns:  Open the Indicators tab, and enter the following:
     • If Data: Indicator contains Negative then change color (select red)
     • If Data: Indicator contains Positive then change color (select green)
7. Click Save and Exit or click its drop-down arrow to view other options.