Waterfall Charts

[eFein]

Waterfall charts are a good way to show how many parts can compose one final number Such as how revenue, minus expenses will create net profit. Here is how to create one of these charts in Looker, starting with some profit and loss data:

When modeling the data in Looker, the trick is, just make one additional row. I just UNION a total row and use a derived table

Old LookML

- view: profit_loss
  derived_table:
    sql: |
      SELECT item_number, item, category, amount 
      FROM profit_loss_table
      UNION
      SELECT 9999, 'Total', 'Total', 0

New LookML

``` view: profit_loss { derived_table: { sql: SELECT item_number, item, category, amount FROM profit_loss_table UNION SELECT 9999, 'Total', 'Total', 0 ;; } } ```

We model this quite straightforward, just creating a measure as needed:

Old LookML

fields:
  - dimension: item_number
    hidden: true

  - dimension: item
    order_by_field: item_number

  - dimension: category

  - dimension: amount

  - measure: total_amount
    type: sum
    sql: ${amount} 

New LookML

dimension: item_number {
  hidden: yes
}

dimension: item {
  order_by_field: item_number
}

dimension: category {}
dimension: amount {}

measure: total_amount {
  type: sum
  sql: ${amount} ;;
}

My explore at first is not very waterfall like. Note: I’ve turned on my totals, we will get to this in the next step.

Then the explore will require some new calculated fields. In my example I just want green for income, red for expense, and a total bar. First, I create those as calculated columns:

Income: if(${profit_loss.total_amount}>=0,${profit_loss.total_amount},0)
Expenses: if(${profit_loss.total_amount}<0,-${profit_loss.total_amount},0)
Total: if(${profit_loss.item}="Total",${profit_loss.total_amount:total},0)

Then I set their colors in the ‘Series’ tab of the visualizaiton settings.
(In pre-series tab versions of Looker, you can change this by specifying the colors you want in the “series colors” box of the style tab, like expense: red )

Now I need to add an offset to bump up each bar by its previous bars. I use two functions: running_total() and offset(). Offset lets me use the previous income line, and the rest I just subtract so the bottoms of the expense and total bars line up.

Offset: running_total(offset(${income},-1)-${expense}-${total})

Now my chart looks something like this:

The data table should have any measures hidden outside of our calculated columns.

I just need to set my offset to “transparent” so that we don’t see it. Click on the color you would like to change in the Series tab, and type ‘transparent’ into the text box.

(Pre-color picker: just add offset: transparent to the style box in the visualization settings. )

You can even pass the transparency to the value labels. I used transparent, white, black, white for this. Check out the final product!