13 Replies Latest reply on Jan 2, 2017 11:20 PM by Charchit Joshi

    Waterfall bridge between months

    Ashleigh Hawkins

      Hi all

       

      After reading numerous Waterfall notes and being new to Tableau (and feeling completely lost on this one) I thought I would post my question to all you tableau guru's out there :-)

       

      I am trying to create a waterfall between 2 periods where I have more than 1 measure and the number of countries which would form the bridge between my 2 pillars could change (I saw an example that used different worksheets that you then consolidated on the dashboard to get the waterfall - but this would not work for me because the number of countries could increase or decrease). I also saw an example which showed that the data had to be reshaped - unfortunately this is not an option in excel before importing the data. I am not sure if you can use a calculated field to somehow do this?

       

      Anyway - I have attached a picture of what I am trying to achieve and also a sample of the data and parameters etc that I have setup. Please note that there is only Aug & Sept sample data included

       

      Feel like I am banging my head on my laptop....so if there is anyone out there who is able to assist I would be very grateful?

       

      Thanks in advance

       

      Ashleigh

        • 1. Re: Waterfall bridge between months
          Luciano Vasconcelos

          Hi.

          You'll need to improve a little bit.

          • 2. Re: Waterfall bridge between months
            Ashleigh Hawkins

            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

            • 4. Re: Waterfall bridge between months
              Wesley Magee

              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

              • 5. Re: Waterfall bridge between months
                Shinichiro Murakami

                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

                1 of 1 people found this helpful
                • 6. Re: Waterfall bridge between months
                  Yuriy Fal

                  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

                  2 of 2 people found this helpful
                  • 7. Re: Waterfall bridge between months
                    Ashleigh Hawkins

                    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

                      Very nice approach!

                      • 9. Re: Waterfall bridge between months
                        Shinichiro Murakami

                        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

                          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

                          • 11. Re: Waterfall bridge between months
                            Yuriy Fal

                            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.

                            • 12. Re: Waterfall bridge between months
                              Shinichiro Murakami

                              Yuri,

                               

                              Thank you for the confirmation.

                               

                              Thanks,

                              Shin

                              • 13. Re: Waterfall bridge between months
                                Charchit Joshi

                                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.