-
1. Re: Waterfall charts including yearly comparison & segment performance
Shinichiro MurakamiOct 11, 2016 7:06 AM (in response to Benjamin Moerman)
1 of 1 people found this helpful -
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.
-
3. Re: Waterfall charts including yearly comparison & segment performance
Shinichiro MurakamiOct 11, 2016 9:52 AM (in response to Benjamin Moerman)
3 of 3 people found this helpfulEasier 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
-
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.
-
5. Re: Waterfall charts including yearly comparison & segment performance
Shinichiro MurakamiOct 12, 2016 6:39 AM (in response to Benjamin Moerman)
1 of 1 people found this helpfulSeveral 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
-
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
-
7. Re: Waterfall charts including yearly comparison & segment performance
Shinichiro MurakamiOct 12, 2016 8:00 AM (in response to Benjamin Moerman)
2 of 2 people found this helpfulI 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