-
1. Re: Waterfall bridge between months
Luciano Vasconcelos Oct 3, 2016 11:33 AM (in response to Ashleigh Hawkins)Hi.
You'll need to improve a little bit.
-
Waterfall Example.twbx 44.3 KB
-
-
2. Re: Waterfall bridge between months
Ashleigh Hawkins Oct 3, 2016 12:44 PM (in response to Luciano Vasconcelos)HI Luciano
Thanks but unfortunately this does not do the trick - the waterfall needs to bridge between the 2 pillars. So Aug closing balance plus or minus the differences between the 2 months (ie Diff measure) would end up with the grand total. The differences should not start at the base of the 1st pillar but at the top of the first pillar and the last waterfall segment (for ZAR) should end at the top of the last pillat. This is what I am having difficulty achieving.
Any thoughts?
Thanks for taking the time to look at it though.
Ashleigh
-
3. Re: Waterfall bridge between months
Luciano Vasconcelos Oct 3, 2016 12:53 PM (in response to Ashleigh Hawkins)No thoughts.
Sorry.
-
4. Re: Waterfall bridge between months
Wesley Magee Oct 3, 2016 2:17 PM (in response to Ashleigh Hawkins)Ashleigh,
I took a shot at recreating your graphic, but I'm not sure I was able to stay true to your intent. Essentially, I used the [Value] field as the primary metric on my rows shelf and then did a running sum using a Gantt bar, which creates the waterfall look when you put Period and Country as your columns. The last two tweaks I had to make were add [Value] to the Size marks card and making an equation that would interpret the current month as negative. It would look like this and would be placed into your rows shelf:
If [Month Parameter] = Month([Period]) Then -[Value] Else [Value] END
This is what my result looked like:
I've also attached the file (v10) for reference. Let me know if this isn't what you were looking for. I'm not sure it can be accomplished with two measures.
-Wesley
-
Waterfall Example (WM).twbx 29.7 KB
-
-
5. Re: Waterfall bridge between months
Shinichiro MurakamiOct 4, 2016 12:02 AM (in response to Wesley Magee)
Little(???!!!) bit tricky because the list of company is different from the time period.
First of all, increasing chart and decreasing chart cannot be combined because there might be same country.
And draw positive chart and negative chart is kind of mirroring, but easily get confused.
Anyways...
[Country Positive]
if {fixed [Country]:sum([Current Month Closing])}-
{fixed [Country]:sum([Previous Month Opening])}>0 then [Country] else "" end
[Country Negative]
if {fixed [Country]:sum([Current Month Closing])}-
{fixed [Country]:sum([Previous Month Opening])}<0 then [Country] else "" end
[Run_Sum P]
attr({fixed : sum([Previous Month Opening])}) +running_sum([Differnce Positive])
[Run_Sum size P]
if index()=1 then 0 else -([Run_Sum P] -lookup([Run_Sum P],-1))end
[Run_Sum size N Label]
-[Run_Sum size N]
[Run_Sum N]
attr({fixed : sum([Previous Month Opening])})+attr({fixed:sum({fixed[Country]:([Differnce Positive])})})
+running_sum([Difference Negative ])
[Run_Sum size N]
-[Difference Negative ]
[Run_Sum size P Label]
-[Run_Sum size P]
Create dual axis chart
primary as bar
secondary as gantt
And sync axis an hide 2nd header
Fix the range.
<< Bar Chart>>
[Country Positive] color
Blank = Blue (or whatever)
All other Country = "White" (looks transparent to hide basic height)
<<Gantt Chart>>
Country Color = somotihng like blue or green to show positive.
Follow the similar step for Sheet "Negative" in the attached file.
And merge two sheets in dashboard.
Thanks,
Shin
-
Waterfall Example SM_9.3.twbx 68.9 KB
-
-
6. Re: Waterfall bridge between months
Yuriy FalOct 4, 2016 11:09 PM (in response to Ashleigh Hawkins)
Hi all,
Let me offer another approach.
I couldn't see any solution to a Waterfall (using Gantt)
where the Dimensions are in a perfect shape.
Typically, opening bar is coming from the other dimension
(at leat in a business sense), so Scaffold with Blend is needed.
That's what I've done here.
Please find the attached.
Hope it could help.
Yours,
Yuri
-
Waterfall Example_YF.twbx 109.0 KB
-
-
7. Re: Waterfall bridge between months
Ashleigh Hawkins Oct 5, 2016 1:11 AM (in response to Yuriy Fal)Hi Yuri
This is exactly what I was looking for...would you be able to provide notes on the steps you take to get this?
Thanks
Ashleigh
-
8. Re: Waterfall bridge between months
Luciano Vasconcelos Oct 5, 2016 5:22 AM (in response to Yuriy Fal)Very nice approach!
-
9. Re: Waterfall bridge between months
Shinichiro MurakamiOct 5, 2016 6:54 AM (in response to Yuriy Fal)
Yuri,
Thank you for providing excellent approach always.
BTW, I tried to reproduce the workbook, but one question.
Is the final sort manual or do you have dynamic sort key in this case?
Thanks,
Shin
-
10. Re: Waterfall bridge between months
Ashleigh Hawkins Oct 5, 2016 9:38 AM (in response to Yuriy Fal)Hi Yuri
I have worked through your workbook and it is great. thanks. The only question I have is that in the original data source I create a dimension for region that looks up the country and allocates it to a region using a case statement...I have added it to your workbook.
The filter from the original source is used throughout my work book on various sheets and I need it to apply to the waterfall. So I tried creating the dimension in the scaffold and blending the region dimension. I then added the region from the original data source as a filter.
The problem is that the 'Opening' & 'Closing' items are returning a null value. Is there a way to amend this so when I apply the region filter it will apply to the waterfall?
Thanks
Ashleigh
-
Waterfall Example_YF.twbx 109.8 KB
-
-
11. Re: Waterfall bridge between months
Yuriy FalOct 7, 2016 10:44 AM (in response to Ashleigh Hawkins)
Hi Ashleigh,
Is it by chance that you're selecting
only one Region (OR All Regions)?
If (luckily) so, then Cross-Datasource filtering
is possible in your case (using Tableau 9.x)
with the help of Region Parameter.
If not -- and it means that you're using
a Region Filter across your views/dashes,
then there's no option other than upgrading
your workbook to Tableau 10.x version.
With that you'd be using Cross-Datasource Filters
out-of-the-box (no hard-coded Parameters needed).
Doing so you'd be blending on the Region, too.
Either way, you'd be modifying the Scaffold datasource
to include a complete Country-Region domain --
having both Opening & Closing rows paired with Regions.
Please find the attached v9.3 & v10.0 workbooks,
and a modified Scaffold datasource (as an Excel file).
Please check the revised calcs in Scaffold+ datasource,
especially in the version 9.3 of the workbook.
Version 10 requires fewer changes, and it's easier to trace.
Hope this could help.
Yours,
Yuri
PS The specified item was not found., the answer to your question is:
Yes, it is the semi-automatic sort, as long as
it requires setting a reasonably high number
to the [YF : Value for Sorting] Parameter --
to cover the whole (MIN-MAX) range of values on a view.
-
Sheet 7.xlsx 31.4 KB
-
Waterfall Example_YF_v10.0.twbx 103.7 KB
-
Waterfall Example_YF_v9.3.twbx 147.9 KB
-
-
12. Re: Waterfall bridge between months
Shinichiro MurakamiOct 7, 2016 11:42 AM (in response to Yuriy Fal)
Yuri,
Thank you for the confirmation.
Thanks,
Shin
-
13. Re: Waterfall bridge between months
Charchit Joshi Jan 2, 2017 11:20 PM (in response to Ashleigh Hawkins)Hi,
I could find a work around around this, which may need a bit of change to your flat file as well.
Change in Flat File:
From: To:
2) Create the waterfall chart using the grant bar and -Value for Metrics till Delta Tax and Interactive. Add EV Baloon in the Row as a Bar chart. Change to dual axis and synchronize both the axis.
3) The axis label would show "Null" for EV bar. Edit alias to EV
Attached is the worksheet. Please let me know your views if the workaround is fine.
-
Waterfall EV.twbx 20.4 KB
-