1 2 Previous Next 25 Replies Latest reply on Mar 14, 2017 3:32 PM by Akshay A

    Waterfall chart with multiple pillars and multiple measures

    James Lapp

      Hi, I have data which lends itself perfectly to a waterfall chart. However, the data will include multiple pillars and each bar in the waterfall is a separate measure. The waterfall trainings on Tableau's website don't apply to this type of chart since they either (A) don't multiple pillars or (B) have multiple measures, or both.

       

      Here's what the data, along with what is a pillar and the calculations. (I've replicated the below in Tableau, with each row a calculated field). Basically, the analysis is taking a previous year's cost (A), adjusting it based on drivers (B) to create a projection of what cost should be in the current year (C) and then comparing that to what the actual current year costs are (D), with the variance being the difference between actual and projected (D-C)

      Pic 1.PNG

      I created a waterfall chart in Excel. I need to replicate this in Tableau:

       

      Capture.PNG

       

      There does not seem to be a simple way to do this. Any thoughts? I've attached Tableau workbook. See tabs Sheet 7 - Sheet 10 for what I've done so far. I've explored breaking some of the bars into separate charts and bringing them together in a dashboard but haven't been able to make it work.

        • 2. Re: Waterfall chart with multiple pillars and multiple measures
          James Lapp

          Hi Lauxman,

           

          Thanks. Yes, I saw the first two threads you mentioned. They are not exactly what I'm looking for since they don't have multiple pillars. But, more to the point, I don't think reshaping my data (solution in those threads) is an option in my case because all my fields are calculated fields.

           

          My data has two measure fields: Headcount (#) and Cost ($). To calculate the "Driver-HC" field in my workbook for instance (a bar in the WF diagram), I take the difference between the average (not sum) headcount in FY14 and in FY15, I then calculate the average cost per person in FY15 by dividing sum of FY15 Cost ($) by the average number of people, and then I multiply the difference in headcount from step 1 by the average cost per person in step 2 to get to my final "Driver-HC" field.

           

          Even though this calculated field is simple arithmetic, it involves aggregation of the data, I'm not sure data reshaping would work.

           

          I could do all these calculated fields offline but it completely defeats the point of using tableau since the fields wouldn't be dynamic.

          • 3. Re: Waterfall chart with multiple pillars and multiple measures
            Brian Calvanese

            For simple data reshaping without restructuring the source, I've used 'data scaffolding' several times and owe  Joe Mako a serious debt of gratitude for his terrific TDT session:

             

            TDT: Data Scaffolding with Joe Mako

             

            For your waterfall, I created a new 'waterfall scaffold' data source:

             

            Waterfall Scaffold Source.PNG

             

            Blending your data onto the scaffold, I was able to create your waterfall:

             

            Waterfall Scaffold.PNG

            Some noteworthy calculations:

             

            For each of the Waterfall Elements, I brought over the values from your data set to the waterfall scaffold (one example shown below):

             

            [Grand Total - FY14] =

            SUM([3. Gross-Reg Labor (Simplified)].[Grand Total - FY14])

             

            All of the waterfall values are different variations of the following case statement:

             

            [Waterfall Value] =

            case attr([Waterfall Element])

            when "Grand Total - FY14" then ([Grand Total - FY14])

            when "Driver - HC" then ([Driver - HC])

            when "Driver - Merit" then ([Driver - Merit])

            when "Driver - Union GWI" then ([Driver - Union GWI])

            when "Driver - Storms" then ([Driver - Storms])

            when "Projected - FY15" then ([Projected - FY15])

            when "Variance" then ([Variance])

            when "Grand Total - FY15" then ([Grand Total - FY15])

            end

             

            The waterfall base is probably the trickiest part:

             

            [Waterfall Base] =

            if attr([Waterfall Element])="Variance" then lookup([Waterfall Pillars],-1)+zn([Waterfall Downs])

            elseif zn([Waterfall Pillars])>0 then null else

            window_sum(zn([Waterfall Pillars]),first(),0)

            +window_sum(zn([Waterfall Downs]),first(),0)

            +window_sum(zn([Waterfall Ups]),first(),-1)

            end

             

            Hope this helps!

            Brian

            1 of 1 people found this helpful
            • 4. Re: Waterfall chart with multiple pillars and multiple measures
              Steve Martin

              Hi James,

               

              I had to put together a bit of a complex one last year, does this help?

               

              This does not need a scaffold per se and is quite quick to build. The benefit of using indexes means that you can place the measures in any order.

               

              The workbook is downloadable and contains instructions on how it was built

               

              | Tableau Public

               

              Steve

              3 of 3 people found this helpful
              • 5. Re: Waterfall chart with multiple pillars and multiple measures
                Koti D

                Hi Brian,

                i am new to tableau ,i got same requirement ,how to achieve this?

                my data source:

                data.JPG

                required o/p:

                reqop.JPG

                 

                regards,

                kd

                • 6. Re: Waterfall chart with multiple pillars and multiple measures
                  Steve Martin

                  Hi Koti,

                   

                  If it helps, my waterfall I mentioned in the earlier post covers this without the need to either blend the data or build a data scaffold or to restructure the data.

                   

                  The above link appears to be dead: https://public.tableau.com/views/superstorewaterfalldemo/Dashboard1?:embed=y&:display_count=yes&:showTabs=y

                   

                  Steve

                  • 7. Re: Waterfall chart with multiple pillars and multiple measures
                    purna.kanagala

                    This worked like a charm, that's awesome how you would come up with this trick.

                     

                    Really appreciate it.

                    • 8. Re: Waterfall chart with multiple pillars and multiple measures
                      Steve Martin

                      Hi Purna,

                       

                      Apologies for the very late response, Tableau did not inform me that you had responded to me.

                       

                      I'm really glad it worked for you. To answer your question, I work quite extensively with cubes which are extremely restrictive when it comes to simple logic calcs; the experience of which taught me how to manipulate Tableau's output using the Index(), Last() and First() table calcs, once you understand these, Tableau open's right-up.

                       

                      So when a Qlik expert challenged me a couple of years ago with a chart that took him an hour to put together, I used his data and put this together in under 10 mins - that'll learn him

                       

                      Steve

                      • 9. Re: Waterfall chart with multiple pillars and multiple measures
                        lenzy petty

                        Hi Steve, what you've shown seems to be very close to what I am trying to achieve, but I can't quite figure out how to apply it to my scenario.

                         

                        The data in Green is my raw data.  Blue is my Aggregates created in Tableau.  Yellow is my Year over Year change by car company, created using Tableau Quick Calcs.  The Years are a Date Field, and I can drill down to quarters and months.

                        I have over 30 car companies, but have made a small list here for this example.

                         

                        1. I know how to build the first table, with Year over Year change (Yellow) and the Grand Totals (Blue) from my data.

                        2. I don't understand how to build Table #2, which combines the Grand Totals (Blue) and the Year over Year change (Yellow).

                        3. I don't understand how to transform Table #2 into a waterfall chart like Chart 3.

                        4. In my scenario, the car company names change based on the geographic region that is selected, and I can have as many as 20 car companies in one waterfall chart/bridge.

                        • 10. Re: Waterfall chart with multiple pillars and multiple measures
                          Steve Martin

                          Hi Lenzy,

                           

                          Q2 & 3:

                           

                          Did you take a look at the post I have provided the link for? Download a copy of the workbook as this tells you how to build the waterfall as in, we are manually building a waterfall he from scratch including telling Tableau what to put where.

                          Once you have worked-out what you are building, the rest is elemental for example, assuming your data looks similar to your data in table 1, your calculation for the Total for 2015 uses the Total function so:

                           

                          [Year 2015] = Total(Sum([2015]))

                           

                          This returns a single value of 174 which you will then plot onto the first column of the waterfall.

                           

                          Q4: As this chart is building a waterfall from scratch, you need to define your outputs at every stage so, as such, this chart will not allow you to dynamically change the inputs.

                           

                          That said, looking at your desired output, it looks as though you only need a standard waterfall chart that Tableau can do really well rather than this heavily customised waterfall, and a standard waterfall can easily change based on your data.

                           

                          For a standard waterfall:

                           

                          1. Drag your manufacturers to your columns
                          2. Change your chart to gant
                          3. Create your year over year [YoY] if not already provided eg [2016] - [2015]
                          4. Create a new field something like YoY (neg) this is to create the treads of the waterfall and is literally the negative of the YoY vale for which the calc is: -[YoY]
                          5. Now drag your [YoY] calc to the rows and add a running sum table calc
                          6. Drag your [YoY (neg)] field created in step 4 onto the size this creates the drops
                          7. Drag your [YoY] field onto the colour to illustrate positive and negative changes
                          8. And finally to avoid confusion, modify your tooltip something like this:

                           

                          <Company>

                          Year Over Year: <SUM(YoY Diff)>

                          Cumulative: <Running Sum of SUM(YoY Diff)>

                           

                           

                          Steve

                          • 11. Re: Waterfall chart with multiple pillars and multiple measures
                            Akshay A

                            Hi Brian, Thank you for this amazing workaround. It surely helped me a lot. I have a question for you incase you could help please. Same chart if I were to bring the header names down on x-axis and not at the top. Is that possible.

                             

                            As i need to show the variance on the top and Measure Names below

                             

                            Thank You for your time

                            Akshay

                            • 12. Re: Waterfall chart with multiple pillars and multiple measures
                              Maarten Stam

                              Hello Steve, thanks for providing the awesome waterfall demo and "charming" solution for our finance department. However, they are complaining about including the "null" field when filtering. I have been breaking my head for the past couple of days how to bypass this caveat to no resolve and was wondering if you have any ideas how this can be solved? Thanks again for sharing the waterfall demo... the best I came across. Thanks for your help and time.

                              Maarten

                              • 13. Re: Waterfall chart with multiple pillars and multiple measures
                                Steve Martin

                                Hi Maarten,

                                 

                                I am delighted you and your company are enjoying using this.

                                On to your question, off the top of my head, there are two ways this ugly member can be removed though neither is elegant so I shall have to think a little more into it:

                                 

                                1. Simply rename the field to a blank "" although this will display in the filters as an empty checkbox; you could always name this more descriptive such as "Keep Checked" either way, this is both ugly and can be confusing to end users.

                                2. A second approach which will leave more pleasing results would be a parameter and calculated field:
                                  - Create a new parameter (again I shall be using Superstore) pRegion and populate from the field [Region] only remove the Null
                                  - Create a calculation fRegion: [Region] = [pRegion] or [Region] = 'Null'
                                  - drop [fRegion] to your filters and select true
                                  - Finally add the parameter pRegion to your dashboard, the null has been removed from the list of allowed values but shall always be selected no matter which member is chosen to filter on

                                 

                                Approach 2 will certainly be more pleasing to use but for the limitation of only showing one member per time unless you define a multiple entries to cover all of your bases.

                                I shall continue to think about this but hopefully, this solution should get you closer to the mark of removing the 'Null'

                                 

                                Steve

                                • 14. Re: Waterfall chart with multiple pillars and multiple measures
                                  Maarten Stam

                                  Hi Steve,

                                   

                                  Thanks for the swift reply. Option number 2, I already deployed using a parameter, but leaves out the option of multiple selections (other than "pre-thinking" user interactions by defining multiple entries - which is to no avail). I have spent a lot of time searching the forum and internet extensively and only found one solution by using the an URL action to Tableau Server to include the "NULL" record, but was not able to reproduce. So, can I conclude there is no straight forward alternative by using 1) a combination of two sets (one containing the "NULL" record or 2) building a condition in the filter form using "By formula"? Thanks again, hope to hear from you.

                                   

                                  Maarten

                                  1 2 Previous Next