Question

Waterfall Charts

  • 20 February 2016
  • 16 replies
  • 1813 views

Userlevel 4

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!




16 replies

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

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

Userlevel 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.

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

Userlevel 4

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!

Is this still possible with the new series editor?

Userlevel 3
Badge

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.



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 🙂



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

Userlevel 4
Badge

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:


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.

Userlevel 2

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 help.looker.com



Thanks!

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.

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

Userlevel 7
Badge +1

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.

Userlevel 4

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! 🙂

Hello.


This has been a very useful document.


Is there anyway to disable hover values, just as the transparent padding shows a value when hovered over?

Reply