7 Replies Latest reply on Oct 12, 2016 8:00 AM by Shinichiro Murakami

Waterfall charts including yearly comparison & segment performance

Benjamin Moerman Oct 11, 2016 6:42 AM

Hello everyone,

I am looking to build a waterfall chart which compares a performance between two years, the focus being on which industry/Segment/departments/... is responsible for the performance. It's often used by Finance/Business people in companies, and could not find anything clsoe enough existing (Starting point was 0, final point was missing, comparing years without industries...).

The catch is: I cannot change the way the data is structured (and exporting in Excel to have it in Tableau again is not a solution). I made a simple twbx datasource attached, which is similar to a lot of companies data: A profit field, a date field, and an industry field.

The goal is to have something similar to that printscreen below I did in Excel. The design is quite easy in Tableau, using a running total, in gant mode with profit equals: -profit. Done.

However, I struggle to have my 6 elements as one single dimension, which is a requirement for the running calc. Tried plenty of calculated fields, LODs, filters but stopped having ideas at some point.

Any idea how to solve my issues, I would appreciate it (and I am pretty sure other people would find it useful also )

Thank you,

Ben.

Waterfall including date filter.twbx 30.1 KB

1. Re: Waterfall charts including yearly comparison & segment performance

Shinichiro Murakami Oct 11, 2016 7:06 AM (in response to Benjamin Moerman)

This recent post should be similar?

Waterfall bridge between months

Thanks,
Shin
1 of 1 people found this helpful
Like Show 1 Likes(1)

Actions
2. Re: Waterfall charts including yearly comparison & segment performance

Benjamin Moerman Oct 11, 2016 8:13 AM (in response to Shinichiro Murakami)

Hello Again,
Thanks for your answer.

Well. I indeed read the post and Yuriy's solution looks indeed exactly what I need in terms of design and final result. However, I have no idea what is the data scaffolding and how to do it. I will read a few posts and tutos as it does not look intuitive and give it a try.
Does it imply to export in Excel and copy the datasource several times? The data I use is a SQL server with several million lines. A bit scared regarding the datablending of several datasources when there is a huge amount of data + the idea is to have something automated that updates when the new daily data are refreshed (so no manual step)

If It's in fact not working and implies too many manual steps, I will try the solution you offer which is less esthetic (sorry about that) but looks more automated.

I let you know tonight,
Cheers,

Ben.
Like Show 0 Likes(0)

Actions
3. Re: Waterfall charts including yearly comparison & segment performance

Shinichiro Murakami Oct 11, 2016 9:52 AM (in response to Benjamin Moerman)
Easier way.
Create three workbook and combine on Dashboard.

The key is to add reference line / Headers to align the height of three graphs.

[2015 Profit]
{fixed:sum(if [Year]=2015 then [Profit]end)}

[2016 Profit]
{fixed:sum(if [Year]=2016 then [Profit]end)}

[Delta text]
sum(if [Year]=2016 then [Profit] end)-sum(if [Year]=2015 then [Profit] end)

[Delta]
-[Delta text]

[Fall graph]
attr([2015 Profit])+running_sum([Delta text])

[Ref line as Max]
max([2015 Profit],[2016 Profit])

[Last year] // need to prepare header
"2015"

[This year]
"2016"

Add to Dashboard

Hide header for middle and right chart.

Thanks,
Shin

Waterfall including date filter_SM_10.0.twbx 44.5 KB

3 of 3 people found this helpful
Like Show 3 Likes(3)

Actions
4. Re: Waterfall charts including yearly comparison & segment performance

Benjamin Moerman Oct 12, 2016 3:06 AM (in response to Shinichiro Murakami)
Hi Shin,

Once again thanks for that incredible job, very helpful and clear, learnt a lot. I did some testing on that database and a few other ones (with real data), I have a few comments (I really tried a lot of things!)

1. the ref line as max is not working for the waterfall tab. If you change your "Ref line as Max" by 10000000, nothing changes (I see the logic behind it though).I changed the dataset to show more growth and you see a disconnection between your Max working for both years, but not for the waterfall.
(see below, 60K and 70K not aligned).

2. A possibility to zoom/filter could be interesting (and I am pretty sure most users would agree). I added 2 Salesmen in the database. I would like to be able to filter on either one of them in the final dashboard. In reality, it could be a list of 100 people, so a parameter or calc field using their names does not appear as a good solution.

3.I would like to filter on year to date/year on year data. Meaning, we have September data, I would like to compare Jan-Sept 2015 to Jan-Sept 2016. I created a True/False formula doing the job and working fine for the waterfall. I did not find how to have it in the 2015 and 2016 tab, as this LOD is not a big fan of double conditions with 2 different types of data.

I am going closer to the goal, thanks again for your help.
Cheers,

Ben.

Waterfall including date filter v2.twbx 60.8 KB
Like Show 0 Likes(0)

Actions
5. Re: Waterfall charts including yearly comparison & segment performance

Shinichiro Murakami Oct 12, 2016 6:39 AM (in response to Benjamin Moerman)
Several modification are needed.
See attached file.
To align height, modified a formula of Ref Line.

To make filter work, add some LOD calculations

With that, all the filter needs to be converted to "Context Filter"

Also, Filter should be applied to all the sheet at same time.

Thanks,
Shin

Waterfall including date filter v2_SM_10.0.twbx 76.4 KB

1 of 1 people found this helpful
Like Show 1 Likes(1)

Actions
6. Re: Waterfall charts including yearly comparison & segment performance

Benjamin Moerman Oct 12, 2016 7:43 AM (in response to Shinichiro Murakami)

Hi Shin,

Thanks again for answering so fast and helping us using Tableau. highly appreciated, I owe you a beer.
Quick note: on the dashboard you attached, you did not put the filter "date test" as a context filter.

Last thing, everything works fine for Johhny, but something is incorrect with Bernard in the look of the dash.
It looks like Tableau does not like the change of axis with the ref line when there is a huge difference in the data. Or maybe I have no idea...

Any guess?
Thanks

Ben
Like Show 0 Likes(0)

Actions
7. Re: Waterfall charts including yearly comparison & segment performance

Shinichiro Murakami Oct 12, 2016 8:00 AM (in response to Benjamin Moerman)

I tried to avoid the usage of LOD with performance reason, but in this, seems like one more LOD needed to cover the case all negative.

[Ref line as Max]
max([2015 Profit],[2016 Profit])+
{fixed :sum(if [Delta text]>0 then [Delta text] else 0 end)}

BTW, could you mark the answer as correct.

Thanks,
Shin

2 of 2 people found this helpful
Like Show 1 Likes(1)

Actions