10 Replies Latest reply on Oct 15, 2019 7:23 AM by Soumitra Godbole

    Visualization & scaling

    Kalyan Ram

      Hello all,

      i have stuck with a problem where i wanted to create a kind of waterfall chart.

      In my dashboard i have 3 sheets, these 3 dashboard should scale with each other .

      but it is not working that way. i tried many hacks but couldn't figured out.

       

      thanks.

        • 1. Re: Visualization & scaling
          Ken Flerlage

          I'd suggest that you first turn off the titles, column field labels, and column headers (you'll need to use a legend for that) as those will mess with start and end point of the axes.

          Once that's done, you have a couple of options. First, you can fix the axes on all the charts.

          While this works, it isn't flexible--it will always end the axes as -250,000 and 4,000,000.

           

          We can automate this a bit by using reference lines--essentially we'll create a calculation then use it to create a reference line on each chart. Tableau will adjust the axis to account for the reference line. Since you have negative numbers, we'll need two reference lines. Create the following calculated fields:

           

          Max Axis

          // Get the maximum value then add some buffer so we can control the top axis.
          // LOD needed to get the sum for each media type.
          // Have to use MAX twice since it only takes two inputs.
          // Add a percentage in order to make it slightly larger.
          // A final LOD is needed to ensure we have a single static value.
          {FIXED :
          MAX(
          MAX(
          MAX({FIXED [Media]: SUM([Sim Volume])}),
          SUM([Prev Volume])
          ),
          SUM([Sim Volume])
          )*1.2
          }
          

           

          Min Axis

          // Get the minimum value then add some buffer so we can control the top axis.
          // LOD needed to get the sum for each media type.
          // Have to use MIN twice since it only takes two inputs.
          // Add a percentage in order to make it slightly larger
          // A final LOD is needed to ensure we have a single static value.
          {FIXED :
          MIN(
          MIN(
          MIN({FIXED [Media]: SUM([Sim Volume])}),
          SUM([Prev Volume])
          ),
          SUM([Sim Volume])
          )*1.2
          }
          

           

          Next, be sure that your axes are all set to automatic.

           

          Then drag these two calculations to the detail card on each chart. Then create a reference line for each calculated field on each chart.

          Note that I've turned off the line, label, and tooltip so that it will be invisible--but it's still there.

           

          Once this is done on all three charts, you'll see that it keeps consistent axes.

          See attached.

          • 2. Re: Visualization & scaling
            Soumitra Godbole

            Hello again Kalyan,

             

                What an interesting co-incidence to be meeting again after a few months and for the same topic

            (Waterfall Chart). Also note that several of your data values (for Prev Volume) were wrong and I had

            to make corrections and do some cool mathe-magical ( mathematical ) operations to figure out what

            the actual data should be so I get the result shown in your visual. I did come close (off by 0.2 in 35 million)

             

            Please find enclosed my simple solution that involved LODs & a Table Calculation. I have attached the

            twbx file below and also enclosed the sample excel data spreadsheet (as your numbers were off). I did

            spend sometime going on a wild goose chase but thankfully figured it out after making the corrections.

             

            IMAGE.PNG

             

             

             

            Calculated Fields :

            Just 4 calculated fields did the trick

             

            Formula 1 - Previous (Total).png

            Formula 2 - Negative Sim Volume.png

            Formula 3 - Final (Total).png

            Formula 4 - Running Total.png

             

            Creating the Visuals :

            Apart from creating the 3 visuals we need to make sure the axes are properly set for this problem. Pay

            close attention to the values for the Axes Max & Min  for each visual.

             

            Visual 1 (Previous Total)

            Pic 1.png

             

            Visual 2 (Waterfall)

             

            Pic 2.png

             

            Visual 3 (Final Total)

            Pic 3.png

            Hope this was helpful and entertaining. Do let me know if you have any questions or need further

            clarity on any of the calculations used. In case this answers your question, please mark as helpful,

            correct answer. Thanks !


            Sincerely,

             

            Soumitra

            • 3. Re: Visualization & scaling
              Soumitra Godbole

              Hello again,

               

                  In case you are interested in a different type of Waterfall chart (way more challenging) and a real

              Animated waterfall created by ? (you know who ), check out this thread. It was probably the longest

              story ever told and it seemed never ending but fortunately ended in 227 hrs.

               

              Waterfall

               

              Here are some screenshots

               

              Pic 1.png

              Pic 2.png

               

              Just as a break and to take my mind away from all those calculations I tried to do something fun i.e.

              create an animated waterfall using just 13 images.

               

              Dashboard.png

              Hope this was fun, entertaining, informative and a nice way to spend time. Best wishes !

               

              Sincerely,

               

              Soumitra

              • 4. Re: Visualization & scaling
                Kalyan Ram

                hi ken,

                thanks for your solution.

                i think the workbook that  you have attached,is exactly the same that i have attached....

                • 5. Re: Visualization & scaling
                  Ken Flerlage

                  I just checked and it's the one I modified. I'm attaching it here again.

                  • 6. Re: Visualization & scaling
                    Kalyan Ram

                    THANKS KEN !!!

                    • 7. Re: Visualization & scaling
                      Kalyan Ram

                      Thanks Soumitra...

                      hope we will meet again with new topic...

                      1 of 1 people found this helpful
                      • 8. Re: Visualization & scaling
                        Ken Flerlage

                        No problem. Was my response helpful or did I misunderstand what you needed?

                        • 9. Re: Visualization & scaling
                          Soumitra Godbole

                          Hi Kalyan,

                           

                               You're always welcome ! Just to be fair to the original data, I have updated the earlier

                          posted twbx file with your original data. Also there are 2 different ways of looking at the

                          Waterfall chart from Previous & Current values and and just to have more fun, I went ahead

                          and created the other one as well (so now you have Methods A & B)

                           

                          Here are screenshots of the above 2 different methods

                           

                          Image A.png

                           

                          Image B.png

                           

                          Also the twbx file is attached along with the excel spreadsheet (containing the original & corrected data).

                          Hope this is like an "icing to the cake" in addition to the earlier solution. Enjoy !

                           

                          Sincerely,

                           

                          Soumitra

                          • 10. Re: Visualization & scaling
                            Soumitra Godbole

                            Thanks once again for a wonderful and fun problem and will be awaiting your next problem.

                            I pray it is not a SKY FALLING CHART (Not another Sheldon novel).

                             

                            Lastly I have given this problem a 5 star (* * * * *) rating. Best Wishes !

                             

                            Sincerely,

                             

                            Soumitra