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.
2 of 2 people found this helpful
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?
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.
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.
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.
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.
Tableau Daimler SynxAxis.twbx 136.5 KB
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.
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 <
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:
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:
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:
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:
And they stay lined up, regardless of the filter:
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!
Tableau SyncAxis jtd.twbx 166.5 KB
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.
1 of 1 people found this helpful
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?