Sunday, June 16, 2013

Creating Waterfall Charts in Tableau

Today, we will talk about how to create waterfall charts in Tableau.  A waterfall charts is a good tool for seeing how sequential processes contribute to the whole.  It is very similar to a stacked bar, albeit with a different twist.  Here is a sample we pulled from Google.

Sample Waterfall Chart
For this, we created a sample checking account statement in Excel.  Here is a snippet of it.  In case you were wondering, this is fictitious and in no way reflects the statement of ourselves or anyone that we know of.

Row ID Date Description Amount Daily Posted Balance
2 4/2/2013 Direct Deposit $485.00 $1,596.56
3 4/2/2013 Rent ($804.74) $791.82
4 4/4/2013 Food ($16.00) $775.82
5 4/4/2013 Car ($374.43) $401.39
6 4/5/2013 Cable ($34.23) $367.16
7 4/8/2013 Direct Deposit $1,598.75 $1,965.91
8 4/8/2013 Gas ($39.56) $1,926.35

Step 1:
  • Create a Negative Amount calculation
  • Create the following calculation
Negative Amount
Step 2:
  • Drag MONTH( [Date] ), DAY( [Date] ) and [Row ID] to the Columns Shelf
  • Drag [Daily Posted Balance] to the Rows Shelf
  • Drag [Amount] to the Colors Shelf, play with it if you choose
  • Drag [-Amount] to the Size Shelf
  • Drag [Description] and [Date] to the Details/Tooltip Shelf
Waterfall Chart
Now, we can see a simple representation of our account balance throughout the month.  However, what if your data set doesn't contain a nice [Daily Posted Balance] field.  Have no fear, you can recreate it using table calculations.  Obviously, we would lose the ability to see the start balance, but that's a topic for another post.

Step 1.5:
  • Create a Running Sum of Amount
  • Create the following calculation
Balance
Step 2:
  • Create the same chart as before, using [Balance] instead of [Daily Posted Balance]
  • Also, you will need to place [Description] and [Date] on the Tooltip Shelf this time
Waterfall Chart (with Running Sum)
Look familiar?  It should because it is the same exact chart we just saw, except that we now start at 0 instead of ~1100.  Depending on how your data looks you may have to slightly alter the way you approach this.  There are a ton more cases where you can use Waterfall Charts.  We hope you found this informative.  Thanks for reading.

Brad Llewellyn
Associate Consultant
Mariner, LLC
llewellyn.wb@gmail.com
https://www.linkedin.com/in/bradllewellyn

5 comments:

  1. Hello Brad,

    Is there a way to automatically update a value into set or parameter from datasource,For example: If I want to show YTD I have column which says current Month and other column which says Current year.So this current month column gets updated every month and I have a set which has current and previous years, but I want to add my forthcoming year which will come to data source
    as 201401 as current month and the set should say 2014 which needs to be selected as well. and the year 2012 needs to be deselected.

    Is there any way we can accomplish this in Tableau :)

    Please let me know if I have confused you


    Thanks,
    Kathir

    ReplyDelete
    Replies
    1. Kathir,

      Would something like this work?

      http://breaking-bi.blogspot.com/2013/02/creating-period-to-date-matrix-in.html

      Thanks,

      Brad Llewellyn
      Associate Consultant
      Mariner, LLC
      brad.llewellyn@mariner-usa.com
      http://www.linkedin.com/in/bradllewellyn
      http://breaking-bi.blogspot.com

      Delete
  2. Dear Brad, what do you think about this? http://fincontrollex.com/?page=products&lang=en
    This tool allow to get waterfall charts directly in excel workbook and have some convenient features

    ReplyDelete
    Replies
    1. That's really cool! One of the fun things about Excel is how much you can do with it you really want to. Thanks for sharing.

      Delete
  3. Hi how to create water fall chart overlap or stacked waterfall chart give me solution

    ReplyDelete