1 2 3 4 Previous Next 45 Replies Latest reply on Feb 25, 2017 2:47 AM by Jonathan Drummey

    Synchronize axis between graphs

    Gabriel Fernández

      Hi everybody,

       

      I've been struggling with this for about a month so I could really use the help

       

      I have a dashboard where three graphs are shown simultaneously, I need this graphs to have the same axis range, and at the same time, I need them to be automatic.

       

      Basically, I need the larger automatic range to become the range of all three.

       

      I have read about reference lines, but I can't get it to work with my data because I think my graphs work differently form those used in the examples of reference lines.

       

      Attached is the workbook, and two images explaining the problem.

       

      Thank you very much for the help

       

      Explanation 1.PNG.png

      Explanation 2.PNG.png

        • 1. Re: Synchronize axis between graphs
          KK Molugu

          Gabriel:

           

          I played with a little bit, but had no luck. Since, worksheets are independent on the dashboard, don't know if you can automatically align/sync the axes on all worksheets. The only CRUDE way that I can think of is to have a fixed axis for each worksheet (hopefully you have the lower and upper values). Hope this helps.

           

          ..kk

          • 2. Re: Synchronize axis between graphs
            Jonathan Drummey

            One comment, and one thought:

             

            The EBIT OP al mes calc in the EBIT Sheet 1 worksheet is returning the same result as EBIT OP. With no dimensions in the view, the table calc is acting as a regular measure (there's only one address in a one partition for the calc to compute over, so the running sum only has one value to sum up), so I'm not sure that it's returning the results that you are expecting.

             

            Here's one way to get the same axes that would be dynamic, but not very easy to set up. The goal here is to enable the measures to return the same results in each view, then build a set of calculations that can be used to set invisible reference lines to pin the axes.

             

            - Increase the level of detail in Sheets 1 and 3 to include the Fecha dimension. Get rid of the Tipo dimension from Sheet 2, with a single select filter it's not necessary.

            - Create calculated fields for the EBIT OP and EBIT Real that return the correct level of detail, for example using TOTAL() or WINDOW_SUM()

            - verify that you get the correct results

            - create two additional calcs that return the the largest value and smallest values, respectively, of the measures used.

            - rebuild the views.

            - Use those two additional calcs to create an invisible reference band. Tableau will automatically extend the range of each axis to keep them in sync.

             

            I don't have time to set this up right now, KK Molugu, do you think you could?

             

            Jonathan

            2 of 2 people found this helpful
            • 3. Re: Synchronize axis between graphs
              Gabriel Fernández

              Thanks a lot Jonathan,

               

              I've played with it but got lost in the calculations.

              If I erase the Tipo dimension from Sheet 2, then I have all the values for the 3 dimensions.

              The calculated fields would be to show only the values for one dimension?

               

              I get the calcs for largest and smallest values, these values would be the ones I would use for creating the reference lines. (I'm thing a reference line or band would be the same right?)

               

              Thanks a lot for the help.

              • 4. Re: Synchronize axis between graphs
                Gabriel Fernández

                Thanks Karunaker,

                 

                Unfortunately my upper and lower values are different evry time and even from month to month as the running sum changes. I have kind of succesfully created calculated fields to get the max value of the graph, but I can't assign a reference line to that value on the other graph, because the calculation changes with the graph.

                 

                I think I might be confusing you as much as I am myself.

                • 6. Re: Synchronize axis between graphs
                  KK Molugu

                  Gabriel:

                   

                  I am tied up with few things at work and will give this a shot over the weekend to see if I can do what Johnathan mentioned. Can you attach the updated book, as you might have done work and I don't want to repeat.

                   

                  ..kk

                  • 7. Re: Re: Synchronize axis between graphs
                    Gabriel Fernández

                    Hi Karunaker,

                     

                    Sorry for not writing before and thanks a lot for the help, here is the workbook I am working on. It is not pretty, I added Fecha to the level of detail and got rid of the Tipo filter on Sheet 2, I created the calculated fields for EBIT OP and EBIT Real but they're not showing the values I need and also can't figure out how to show only the columns I need in each graph if I add Fecha to the level of detail.

                     

                    Hope it helps Karunaker, and really thanks again.

                    • 8. Re: Synchronize axis between graphs
                      Hope Barrett

                      I have encountered a very similar challenge where I would like to synchronize the Y-axis across multiple worksheets. I'm looking forward to any progress.

                       

                      Hope

                      • 9. Re: Synchronize axis between graphs
                        KK Molugu

                        Jonathan Drummey

                         

                        I tried what you mentioned, but still can't get the axes to sync. When you have sometime, will you be able to get this setup.

                         

                        ..kk

                        • 10. Re: Synchronize axis between graphs
                          Jonathan Drummey

                          I'll have some time in the next couple of days to take a look. I did an

                          initial look at it, based on the way the data is set up (having multiple

                          levels of detail such as each month and a YTD) this is more complicated

                          than it might be.

                           

                           

                          On Wed, Mar 5, 2014 at 1:14 PM, Karunaker Molugu <

                          • 11. Re: Re: Synchronize axis between graphs
                            Jonathan Drummey

                            See the attached, I didn't try to repeat all of the formatting, I was able to use the process I outlined above, and I relearned something I'd forgotten. Here's what I did:

                             

                            - First, the EBIT OP al mes calc used in Sheet 1 is set up as a RUNNING_SUM table calc. However, there is only one dimensional value in the view (the Tipo) so the RUNNING_SUM doesn't actually do anything. I created a …jtd version that is a regular aggregate. (This also makes life easier for the other calcs).

                             

                            - Because the 3 views have different sets of filters, I had to figure out a way to make one view that returned all the correct results for each Fecha, then use a table calc filter on Fecha (table calc filters are processed after most other calcs are computed) to get the desired results. Also, with multiple levels of detail in the data I had to be careful of what was going on. So I created some row-level calculations that are set up to only return the desired measures like the EBIT Real for Months & YTD that is used in the Sheet 1 tab. In addition, I created 7 table calculations to get the overall monthly min & max for each of EBIT REAL and EBIT OP, and 3 more to get the values of the other three measures. All of the table calculations use a Compute Using of the Fecha. You can see all of this in the workout view:

                             

                            Screen Shot 2014-03-06 at 7.40.42 PM.PNG.png

                             

                            Once I had the workout view created, then I duplicated the worksheet to create the workout bar sheet, moved all the table calcs onto the Level of Detail Shelf, and created a bunch of invisible reference lines & bands. Here's the reference band for min & max EBIT OP across the months:

                             

                            Screen Shot 2014-03-06 at 7.42.26 PM.PNG.png

                             

                            Then I could duplicate this sheet to create the Sheet 1 and Sheet e worksheets. The Sheet 2 worksheet required more work because it's using a dual axis with different mark types, so I had to rebuild all the reference lines. Once that was done, I could put them all on a dashboard and do a little fiddling with the sizes of the headers.

                             

                            However, this wasn't quite syncing the axes, see what's happening with the Sheet 2 worksheet:

                             

                            Screen Shot 2014-03-06 at 7.45.10 PM.PNG.png

                             

                             

                             

                            What I'd forgotten is that Tableau creates the length of the axes using (at least) three factors:

                            1) the extents of the marks

                            2) the extents of the reference lines

                            3) the space needed for mark labels

                             

                            So the axis ranges are getting thrown off by the top-most and bottom-most mark labels, particularly the bottom most labels. When we take out the mark labels or move them to the middle, the axes now line up:

                            Screen Shot 2014-03-06 at 7.52.45 PM.PNG.png

                            And they stay lined up, regardless of the filter:

                             

                            Screen Shot 2014-03-06 at 7.50.24 PM.PNG.png

                             

                            To deal with the mark labels, I'd look into getting rid of them or do something like multiply all the table calcs by a factor large enough to guarantee that they'd leave space for the mark labels.

                             

                            For anyone else doing this kind of view, it's using a bunch of table calcs that could lead to performance issues, so your mileage may vary.

                             

                            Let me know if this works for you!

                             

                            Jonathan

                            3 of 3 people found this helpful
                            • 13. Re: Synchronize axis between graphs
                              Gabriel Fernández

                              Jonathan,

                               

                              I don't even know how to thank you. It's amazing what you did, I'll implement it today

                              Thanks again for all the knowledge and effort you put into helping me, I'm really thankful.

                              • 14. Re: Synchronize axis between graphs
                                Jonathan Drummey

                                You're welcome! Working on this also helped give me an idea on how to describe this kind of work, using fractions as a metaphor.

                                 

                                When when we are trying to add fractions together, like 1/5 and 1/4, we multiply their denominators together to find a common denominator. 5*4 = 20, so 1/5 * 4/4 = 4/20, 1/4 * 5/5 = 5/20, and 4/20 + 5/20 = 9/20.

                                 

                                In Tableau, the level of detail or grain of view is defined by the distinct combinations of values of the dimensions in the view. We can think of that set of dimensions in each view as its own fraction. So if view 1 is at X level of detail and view 2 is at Y level of detail, then when we want to get the same results in both views we're going to need X*Y level of detail in both views. Usually that means that at least one of the views view has more dimensional values (a finer grain) than would return accurate results for the measures in that view, so we need to re-aggregate those measures using table calculations to have the correct granularity.

                                 

                                Does that make sense?

                                 

                                Jonathan

                                1 of 1 people found this helpful
                                1 2 3 4 Previous Next