7 Replies Latest reply on Feb 1, 2018 12:59 AM by Simon Runc

    Ignore secondary measure for automatic axis range

    Tristan Aubert

      Hello everyone,

       

      Do you know if it is possible to ignore a secondary measure when setting the axis range to automatic or, achieve this by some other work around?

       

      The situation I'm faced with is that I've created a waterfall chart using this tutorial which combines a bar chart as the primary measure and a gant chart as a secondary measure to create the waterfall 'steps'. The issue is that these 'steps' are much smaller than the bars (occasionally zero) so if I set the axis range to automatic (even with the "Include Zero" option unchecked) the y-axis will start at zero showing a plot with minuscule steps (this pretty much defeats the purpose of the waterfall chart). (See attached image)

       

      So, back to my original question - is there a way to ignore the secondary measure used for the creation of the steps?  I've seen several forum posts on dynamic scaling such as this one  by Simon Runc however, in most cases these simply push the range 'outwards' using reference lines but not 'inwards'. 

       

      Alternatively is there another way to build a waterfall chart that will not have this scaling issue? The data i'm using has multiple dimension and will require filtering so this may rule out some of the simpler approaches.

       

      Much appreciated!

      Picture1.png

        • 1. Re: Ignore secondary measure for automatic axis range
          Simon Runc

          hi Tristan,

           

          Is there any way you can post a dummy version of your waterfall chart...I'm not 100% sure why the "exclude zero" doesn't do the trick, so would be good to see your setup. There are also (dynamic) ways of limiting an Axis, so happy to take a look (always love a good Axis challenge!)

           

          Here's a video from Joe on how you can create an anonymised workbook (simply)

           

          Anonymize your Tableau Package Data for Sharing

          • 2. Re: Ignore secondary measure for automatic axis range
            Tristan Aubert

            Thanks for the quick reply Simon - seems like you're expert in this area!

             

            I've attached for an anonymized version of the tableau file as you requested. Please note, the data values are slightly different to the one in the screenshot of the original post, however the problem on non-zero axis still persists.

             

            As far as I can tell, if I remove the secondary measure for the gant box sizes (see second tab "_wo_gant") the axis appears to scale correctly, but I lose the waterfall effect.

             

            Thanks again!

            • 3. Re: Ignore secondary measure for automatic axis range
              Simon Runc

              Thanks for the workbook...and it does make sense now. The Gantt size is drawing the gantt marks back to the axis, which is why it always goes to zero.

               

              I've has a bit of a plauy, and can get part of the way there by using your

               

              (SUM([Defaulting Balance Diff])) = LOOKUP(ZN(SUM([Defaulting Balance])), FIRST())

               

              test, to only draw the first mark back to 10%

               

              IF (SUM([parameter_diff])) = LOOKUP(ZN(SUM([parameter_diff])), FIRST()) THEN

              SUM([Defaulting Commitment Diff])*0.1

              ELSE

              SUM([Defaulting Commitment Diff])

              END

               

              but I can't affect the GT...so we get this

               

               

              Close...but no cigar!!

               

              I'll have a bit more of a think, and hopefully come up with something tomorrow (or call in the big guns...ie. Jonathan and/or Yuri!!)

              • 4. Re: Ignore secondary measure for automatic axis range
                Tristan Aubert

                Thanks for quick turnaround Simon - this is already a good start. I'm wondering whether I should create a 'final' step row in the dataset to forgo the need for a total.

                 

                Thanks again!

                • 5. Re: Ignore secondary measure for automatic axis range
                  Simon Runc

                  If you have that freedom...that would make things much easier! We can the use the Last() on Lookup, to pick up that stage & do something similar to draw the gantt back to the point at which the first mark ends.

                   

                  If you can do this, let me know and I can definitely give you a solution!

                   

                  Sent from my iPhone

                  • 6. Re: Ignore secondary measure for automatic axis range
                    Tristan Aubert

                    I do have that flexibility - thankfully - and have updated the dataset to include a 'step_final' data point. I've also made some updates to the way the diff (gant width bars) are calculated and included your suggestion of checking for first() and last() to reduce the scaling and it works like charm.

                     

                    I had just had to do one extra step to ensure the synchronization of the gant height for the first and last bar which I achieved using this ugly formula. It takes the difference between the bar value and  70% of the window minimum to calculate what the gant height should be for the first and last step.

                     

                    -(    

                         IF (FIRST()=0 OR LAST()=0) THEN  (SUM([Defaulting Balance])-(WINDOW_MIN(SUM([Defaulting Balance]))*0.7))

                         ELSE (SUM([Defaulting Balance])-ZN(LOOKUP(SUM([Defaulting Balance]),-1)))

                         END

                    )

                     

                     

                    Thanks again for the help, really appreciate it!

                    success.JPG

                    1 of 1 people found this helpful
                    • 7. Re: Ignore secondary measure for automatic axis range
                      Simon Runc

                      Great work Tristan,

                       

                      I hadn't fully thought through the logic of the "Final Step" adjustment...but knew we'd have to look at what length we set the "Step 1" to and then adjust "final step" so they end at the same point on the axis. Although I hadn't thought through the logic, one thing I did know "I ain't gonna be pretty"!!

                       

                      Some pretty advanced stuff there