Waterfall Charts

waterfall
profit
graph
bar
done

(Eric Feinstein) #1

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!


(Kevin Connolly) #2

Was just making custom waterfall charts (matplotlib via python) this week, this is a great example. Thanks for sharing.


(Ian Hardenburgh) #3

What if the running total results in a value less than zero. I think your calculations need a revision.


(Eric Feinstein) #4

You are right, what if we run a loss?

The method above would end up looking like this:

Not so great!

To fix this I broke up my expense column into two columns: a positive and a negative. It’s not super pretty, but it works.

Here are my new calculations

expense_positive
if(${profit_loss.total_amount}<0,if(offset(running_total(${income}-${expense})>0,-1),offset(running_total(${income}-${expense}),-1),0),0)

expense_negative
if(${profit_loss.item}="Total",0,${expense_positive}-${expense})

I also had to modify my offset calculation and hide my existing expense calculation

offset
running_total(offset(${income},-1)-${expense_positive}-${total})+running_total(offset(${expense_negative},-1))

Then, instead of value labels I added our new totals to each stack. You can read about those here and in our 3.42 release notes.

This can be cleaned up a bit, depending on your use the same method may need to be put into the income calculations, however I always keep those first.


(Ian Hardenburgh) #5

Nice job Eric — now we are cooking w/ gas! It’s not a big deal, but did you notice serious performance degradation after applying the new calcs? Like I said, not a big deal. I could always just handle running totals on source SQL side and pipe calcs in as measures if necessary.

Thanks,
Ian


(Eric Feinstein) #6

It is a lot being done post query, which could impact performance a bit. Perhaps combining some of the calculations could work. If you have developer access, I would definitely move the Income, Expense and Total columns into measures and dimensions as they are very straightforward:

Old LookML ``` - dimension: expense_amount type: number sql: | CASE WHEN ${amount} < 0 THEN -${amount} ELSE 0 END
- measure: total_expense_amount
  type: sum
  sql: ${expense_amount}
  value_format_name: usd

</details>
<details>
<summary>New LookML</summary>

dimension: expense_amount {
type: number
sql: CASE WHEN ${amount} < 0 THEN -${amount} ELSE 0 END
;;
}

measure: total_expense_amount {
type: sum
sql: ${expense_amount} ;;
value_format_name: usd
}


</details>

Or, use a [filtered measure](http://www.looker.com/docs/reference/field-reference#filters):



<details open>
<summary>Old LookML</summary>
- measure: total_expense_amount
  type: sum
  sql: -${amount}
  value_format_name: usd
  filters:
     amount: '<0'

</details>
<details>
<summary>New LookML</summary>

measure: total_expense_amount {
type: sum
sql: -${amount} ;;
value_format_name: usd

filters: {
field: amount
value: “<0”
}
}


</details>


Hope this works!

(Phil Jones) #7

Is this still possible with the new series editor?


(Morgan Imel) #8

Hey @Phil_Jones,

Yep! You should be able to do all these steps using the new series picker. You can change the color of a series to transparent by clicking on the color and typing in ‘transparent’ to the color picker, as pictured below.


(Tomas) #9

Hi all,

I’m really interested in the waterfall charts presented above. Unfortunately, the work-around presented here is not applicable to my data-situation.

I’ve the following mock data (where TruckStatus is a dimension and COUNT a measure):

TruckStatus     COUNT
On Time          12
Delayed          5
Cancelled        3

In other words, my issue is that I do not have a row for the dimension TruckStatus which is similar to the Total row in the above example. Ultimately, I’d like to start with a bar which shows all my trucks (20), which thus is my income, and subsequently subtracting the Delayed and Cancelled counts, where the On Time is my equivalent of profit (or loss).

Somebody who has any ideas on a possible workaround? If more information is needed, please let me know!

Thanks for the help :slight_smile:

PS If this post would better suit a separate topic, please let me know!


(Brecht Vermeire) #10

Hi Tomas!

The best to do this would be to restructure your data so we can add a totals row to the results. Ideally your data should be structured as follows:

Truckstatus Count
On Time     12
Delayed     5
Cancelled   3
Total       20

This can be done in a few ways, depending on how your data is structured in your table. One option would be to create a derived table where we union with a total count:

SELECT
  truck_status,
  COUNT(*) AS amount
FROM table
GROUP BY 1
UNION ALL
SELECT
  “Total”,
  COUNT(*) AS amount
GROUP BY 1

To make sure we order the truck_status from Total to On Time, we can add an extra dimension on the LookML side, that defines the ordering:

dimension: truck_status_order {
    type: number
    sql: CASE WHEN ${TABLE}.truck_status = "Total" THEN 1
    WHEN ${TABLE}.truck_status = "Delayed" THEN 2
    WHEN ${TABLE}.truck_status = "Cancelled" THEN 3
    WHEN ${TABLE}.truck_status = "On Time" THEN 4
    END;;
  }

Once we’re done with this we can start building our waterfall chart. Just like in the original example, we’ll create some additional columns using table calculations. For this example we’ll create the following:

Total: if((${text.truck_status} = "Total"),${text.count},0)
Delayed/Cancelled: if((${text.truck_status} = "Delayed" OR ${text.truck_status} = "Cancelled"),${text.count},0)
On Time: if((${text.truck_status} = "On Time"),${text.count},0)

We’ll create the running total as well, although this one is a little bit different from the original example. This is because we’re starting with the total instead of ending with it. Don’t forget to enable Totals on the table.

if(${text.truck_status} = "On Time", 0,running_total(offset(${total}, -1)-${delayedcancelled}-${total}))

Just like in the original example we’ll make the running total value transparent in the visualisation. And add some color to the visualisation. The end result looks like this:


(Satish Amarapalli) #11

Hi, this chart looks great. Needs some more information from you:

For us we are getting the attributes from more than one table and it’s not row level but in columnar attributes to calculate waterfall chart.
So how to put these tables together and create waterfall chart and also we should be able to filter data based on different attributes

One more question regarding the UI prespective:
While mouse hovering on the bar we are still able to see the Offset values, how we can disable the offset visualization while mouse-hovering. Attached image has highlighted :

Please let me know if any concern.

Thanks,
Satish.


(philip.martinelli) #12

Hi Satish,

If these tables aren’t already joined together, it sounds like addressing your use case will require some lookML/SQL development. This may be best taken to the support team, so please feel free to email support at support@looker.com

Thanks!


(Chenna Reddy) #13

Hi Eric,

This works fine, but I could see an issue in values being displayed in the chart.

If you see the value of “cost of goods sold” displayed is $1200, actually it is $7000. The reason why it is showing 1200 is because “Totals Labels” enabled (this is summing both expenses_positive & expense_negative 4100-2900=1200).

It would be good to have an alternate to display the actual values from the Expense column.


(Dimpi) #14

Hi Brecht,

Is there a way to have axis value labels added to value labels on the bars? Like Delayed 20, Cancelled 15.

Thank you


(Izzy) #15

That’s not currently possible, but you should open a new topic in the Feature Requests section for it! You can edit the tooltip labels (what shows up when you mouseover) by using the html: parameter to add fields, but that does not yet carry over to the value labels.


(Nicholas Wong) #16

Thanks Eric! This is really helpful to visualize BOM of product VS revenue generated. Really awesome. Would love to build this chart too! But sadly, I don’t have the BOM in my Snowflake currently to run this chart. Will be working on it though! :slight_smile: