6 Replies Latest reply on Feb 5, 2016 6:44 AM by Alexandra Zemba

    Waterfall Chart- Using multiple hierarchy levels on one visualization

    Alexandra Zemba

      I am fairly new to Tableau and am trying to create a waterfall chart that shows the total number of prequalified loans and how many of that total are at each stage of the process.

       

      I have a data field that indicates where each loan is in the process (see stages below), which is what I am using as my columns.

       

      The total number of prequalified loans (positive number) should be the base and from there I have 5 stages:

      1. Disqualified- negative number

      2. Rejected- negative number

      3. UW Disqualified- negative number

      4. In Process- positive number

      5. Funded- positive number

       

      The issue I am having is the number of prequalified loans is the total of all loans in each stage listed above. How do I show the Total and the subsequent levels on the same visualization? I tried creating a hierarchy, but that only allows me to look at the Total by itself or drill down to the subsequent levels. I want to use both hierarchy levels on the same chart.

        • 1. Re: Waterfall Chart- Using multiple hierarchy levels on one visualization
          Esther Aller

          Hi Alexandra,

           

          It sounds like you can show the grand totals by navigating to Analysis > Show Row Grand Totals. By turning on the grand totals, Tableau Desktop will show both the total for each stage separately, plus a total of all stages together.

           

          This step in included in the video tutorial on creating waterfall charts, which may be worth checking out.

           

          I hope this answers your question!

          1 of 1 people found this helpful
          • 2. Re: Waterfall Chart- Using multiple hierarchy levels on one visualization
            Alexandra Zemba

            Hi Esther,

             

            Thanks for your response.

             

            I have tried using the grand total to sum all of the stages. However, because I am using a running total calculation, with the negative stages the Grand total is coming out as a negative number. The size of the bar is correct, but the y-axis is starting at -1000 because it is just summing the stages across, not actually using the total as the starting point.

             

            So for example, the total number in all stages is 5,773- this is the first bar I need to see as my base. From there, I need to show the "walk" from each of my stages to funded.

            • 3. Re: Waterfall Chart- Using multiple hierarchy levels on one visualization
              Esther Aller

              Hi Alexandra,

               

              It sounds like the grand totals bar will work for you, except that you need the grand totals to be the first bar. Is this correct?

               

              In Tableau Desktop 9.2, the grand total bar can be moved to the left via the analysis menu. Check out the Move Totals online Product Help entry for more info.

               

               

              1 of 1 people found this helpful
              • 4. Re: Waterfall Chart- Using multiple hierarchy levels on one visualization
                Alexandra Zemba

                When I use the grand total to the left, the visual is right but the y-axis labels and the data label for the grand total bar are not because it is just summing all of the 5 stages across.

                 

                See the visual below. I have 5773 Prequals which is the total of my 5 stages- I need this to be my starting point for the running sum. From there, I need to subtract the 1267 disqualified, then subtract the 1318 rejected, then subtract the 822 UW disqualified, then add the 1029 in process, then add the 1337 funded and that is my end point.

                 

                Using the total, the 5773 is not my starting point, it is just a total of the 5 subsequent stages, so the axis and data labels are incorrect.

                 

                Waterfall Chart.PNG

                • 5. Re: Waterfall Chart- Using multiple hierarchy levels on one visualization
                  Esther Aller

                  Hi Alexandra,

                   

                  Okay I think I finally get it. The grand total should be the total of the absolute value of all of the stages. And then the stages should be the grand total minus the running total, rather than the running total itself.

                   

                  The attached workbook uses some same sample data I made with [Stage] and [Value] using the values you listed.

                   

                  1. Create a calculated field with a name like "Value Adjusted" with a calculation similar to the following:

                    CASE [Stage]
                    WHEN "disqualified" THEN -[Value]
                    WHEN "rejected" THEN -[Value]
                    WHEN "uw disqualified" THEN -[Value]
                    WHEN "in progress" THEN [Value]
                    WHEN "funded" THEN [Value]
                    END

                  2. Create a calculated field with a name like "Value or Total" with a calculation similar to the following:

                    IF MIN( [Stage] ) = MAX( [Stage] )
                    THEN TOTAL( SUM( [Value] )) + RUNNING_SUM( SUM( [Value Adjusted] ))
                    ELSE SUM( [Value] )
                    END

                    The above calculation takes advantage of the fact that the grand totals column has all values of [Stage], and therefore the minimum and maximum value of [Stage] are different for the grand totals bar. For the grand total we want the sum of [Value], which will give us 5773 because no numbers in [Value] are negative. For the rest of the waterfall chart we want to add the running sum of the [Adjusted Value] to the total of the sum of [Value].

                    This calculation will be used on the Rows shelf and will place the bottom of the Gantt bars.

                  3. Create a calculated field with a name like "Negative Value or Total" with a calculation similar to the following:

                    IF MIN( [Stage] ) = MAX( [Stage] )
                    THEN SUM( -[Value Adjusted] )
                    ELSE SUM( -[Value] )
                    END

                    The above calculation will be used on Size and returns the negative values needed to create the appropriate lengths of the Gantt bars. In this case we cannot use -[Value or Total] because the position of the Gantt bar is different from the number of records in each stage.

                  4. Create a calculated field with a name like "Value or Total Label" with a calculation similar to the following:

                    -[Negative Value or Total]

                  5. Drag [Stage] to the Columns shelf. Make sure the stages are sorted correctly.
                  6. Drag [Value or Total] to the Rows shelf
                  7. Change the mark type to Gantt
                  8. Turn on the Grand totals
                  9. Drag [Negative Value or Total] to Size on the marks card
                  10. Drag [Value or Total Label] to Label on the marks card

                   

                  I hope this actually answers your question this time!

                  1 of 1 people found this helpful