8 Replies Latest reply on Mar 30, 2020 3:00 PM by Joe Oppelt

    Aligning axes for separate components of waterfall (walk) chart

    Adam Brinkerhoff

      I've built a waterfall chart based on techniques posted in this forum by Shinichiro Murakami and Joe Oppelt  My solution consists of three separate charts.  At the far left, I have one representing the beginning amount (in this case budget, or "OP"). To its right, I have the variances to budget by product, represented in similar column chart format.  Then, at the far right, I've included another chart those shows the ending amount (i.e., the actual sales for all products, or "Act").  In essence, this solution is intended to show how individual components' variance to budget drove overall performance, as represented below:

       

      walk.JPG

       

      The challenge I have is in aligning the three separate charts' axis values.  In the above, I manually set the min/max axis values for each of the three charts (to show what the solution should look like), but that's not a practical solution.  I tried using reference lines to set both the min and max values.  I can do that fine for OP and Act charts, but I can't seem to do it for the middle charts, since I'm using the window function to calculate the running total necessary to build the gantt chart solution.

       

      ATTR( { FIXED : SUM(IF [POV | Report Period] = 'Y' THEN [Display | Budget] END) } ) + WINDOW_SUM(SUM([vs OP]), FIRST(), 0)

       

      I'm struggling to figure out a dynamic solution, so I was hoping to appeal to brighter minds.  The max value across all three charts should be the top of the bars for Product 1 and Product 2; whereas, the min value should be the value represented at the bottom of the bars for Product 4 and Product 5, but, of course, the data changes each month, so the solution would need to be responsive to of this.@

       

      Attached is the sample workbook (Tableau 2020.1)

       

      Thanks

        • 1. Re: Aligning axes for separate components of waterfall (walk) chart
          Joe Oppelt

          Well you don't have a workbook attached, but you're going to have to calculate that table calc in all 3 charts to find the max and min of the three charts' values for the reference lines.

          • 3. Re: Aligning axes for separate components of waterfall (walk) chart
            Joe Oppelt

            Sorry for the delay.

             

            See attached.

             

            I made a MAX and MIN calc for your Calc1 on the middle sheet.  And its displayed in the title.

             

            Next, look at the third sheet.  I needed to put Product Group onto the Detail shelf so that we had the Calc1 values by Product Group like you have on the middle sheet.  But instead of going with TABLE(across) (which is the default, and which works on the middle sheet), I set the table calc to run along Product Group:

             

             

            But notice the numbers (especially the MIN) don't match what we see on the middle sheet.

             

            Look at the first sheet.  Here I DO have the correct numbers in the title.

             

            In the table calc settings there is a pulldown for Nested Calcs.  Calculation1 is nested within those MIN and MAX calcs.  THis one also needs to run along Product Group.

             

             

            So when we have both calcs running along Product Group, we get the right value for MIN/MAX of the table calc.  You can also put the calc from sheet 1 onto sheet 3, and vide versa.  Thus you can have all three values (actually 4 values since there is a MIN and a MAX for the middle sheet.).  And all these together can let you create the same MIN and MAX reference lines on all 3 sheets.  I leave the rest of the exercise for you to complete.

            • 4. Re: Aligning axes for separate components of waterfall (walk) chart
              Adam Brinkerhoff

              Joe

               

              This is great!  Just one more thing, and I think it'll be perfect.  When I create the MIN and MAX reference lines, the values are 10x the value shown in the title (see below).  Any idea on how to correct that so that they are the same?  It seems like it's multiplying the MIN and MAX amounts by 10, based on the 10 products in the Product Group dimension.

               

              sample1.JPG

              • 5. Re: Aligning axes for separate components of waterfall (walk) chart
                Joe Oppelt

                I'd need to see your calc in your latest workbook.


                Otherwise I can just guess and tell you to divide by 10. 

                • 6. Re: Aligning axes for separate components of waterfall (walk) chart
                  Joe Oppelt

                  Well, wait.  I added the calcs in the workbook I uploaded yesterday.

                   

                  See attached.


                  Will your first and last sheets always be using the same product group?  I had to put IF-logic in my MIN MIN and MAX MAX calcs to look only at them to get the MIN and MAX calcs to work.

                   

                  But I get the same MIN and MAX on the first two sheets in the attached.

                  • 7. Re: Aligning axes for separate components of waterfall (walk) chart
                    Adam Brinkerhoff

                    Ok.  That was easy.  I've set the reference lines at the MIN and MAX values, then I  unchecked the "Include zero" box in the Edit Axis window.  Problem is, since we added Product Group to the table calculation the graph now has the individual product components in the stacked column.  My hope was that when I excluded zeros, the min of the axis would be based on the MIN value your formula produced, and the max value of the axis would reflect the MAX formula, but it doesn't.  I am able to exclude zeros and reset axis values based on reference line values in the middle chart, just not the end charts.  I wonder if I'm looking for something that isn't technically possible.  Here's the current work product.

                    • 8. Re: Aligning axes for separate components of waterfall (walk) chart
                      Joe Oppelt

                      I have a bit of questions about your data.  Here is a screen shot of a piece of your Actual sheet.  I turned the axis back on.  On that sheet I don't get the compartmentalization by Product Group, so apparently on that one only one Product gets data.  But Look at this screen shot:

                       

                       

                      The axis shows a value of 187, but the tooltip shows 49.  Not sure what you really expect to be seeing there.


                      In the previous iteration of your data the OP sheet also had only Product 1.  (That's why I asked if those two sheets will always use the same product.  But your new data tells me that's not the case.)

                       

                      On the OP sheet you do have multiple Products in there, which is what you raised to me in your last reply.  To deal with this we need a new way to display things.  (You still need Product on the sheet to re-calc what we get from the variance sheet. )  And since that compartmentalizes your bar, here is what I did:

                       

                       

                      First, when I display the axis, I see the sum of all of the products is 191.  And if I take Product off the sheet, the overall sum totals to 191. So that's what I'm aiming for.  (Or at least I hope so!)  I made a window_sum calc to sum up all the products.  When I displayed that in TEXT, I got a total of 191 displayed on each Product partition.  (I made the same for Actual, but if you have only one product ever, this is superfluous.)  I also made my own max and min for the variance sheet.  I didn't understand the extra math you were doing in the old calcs I had done for this.

                       

                      In the title of the OP sheet, I have displayed all those values.

                       

                      Now that the WINDOW_SUM is getting displayed uniformly in all the product partitions, I really only need to display the first one.  Look at the INDEX calc I put on filters.  This says to display only the first product.

                       

                      (Special note: Now each and every table calc is getting run along Product on this sheet.)

                       

                      Finally I replaced the calc on ROWS to display the table calc.

                       

                      And then I made my own MAX MAX calc to grab the max of the three sheets.  On the OP sheet now, we have a bar displaying 191, and we have a reference line for MAX that is 193.

                       

                      THe same exercise for MIN MIN would arrive at 187.  (But I wonder if we really need that.  The only way it would be important is if numbers could go negative.

                       

                      See attached