Thank you, Shawn, I think this is the correct answer, and will try it out as soon as possible.
It would be much better if this could be set automatically. Therefore I have created an idea asking for dynamic table partitioning as a built-in feature.
See idea here: http://community.tableau.com/ideas/1565
PS! I tried to refer to this thread in the idea, but <Tableau Create Idea> crashed everytime I did that
Solution works as intended
The solution you showed me works almost perfect as demonstrated in attached workbook. The only minus is that one of the hidden sheets takes some white space while the other one does not.
I cannot figure out how to highlight running total year to date (per year) with comparisons table down per month. I think I would know what to do if it was possible to use a calculated field as partition sort, but seemingly that is not possible.
I have created a new question asking for help regarding this issue, see Table Calculation - Calculate Across, Rank Down | Tableau Support Community
I made some comments on your Idea, one note is that you can actually get the results you want in a single worksheet.
Thank you, I am happy to hear that.
I will try it out. If I succeed I attach the file in idea and here. If not, I hope I can ask you for help.
I am very happy to read your one word answer
Here are my responses to your responses in the Idea, I'm putting them here because I can attach the workbook. My responses are preceded with >>>.
- In your workbook, you have separate layout containers for each of the worksheets. If you put them both into a single layout container, the amount of white space will significantly decrease, though there will unfortunately still be some.
I have attached a new workbook where I made absolute sure that all sheets, horizontal layers, and text are in the same vertical layer. The sad white space is still there in full size. The 'sad white space' can be made smaller by moving the legends below or beside. This is why I chose to put it in the top - it illustrates the weakness better
>>> The problem with the horizontal layers in that case is that you had blank spaces. If you get rid of those and just have the legends, then you save a few more pixels. There are two interconnected issues - one is that the color legends don't really vanish at all, the second is that there are those few pixels left from each worksheet.
If you really wanted the marks on top, one option would be to BYO color legend, either by using a graphic tool like PowerPoint to create a static image or by generating your own worksheet using Square, Circle, or other marks. Then you could have that at the top of the view, and the only moving up/down around would be about 10 pixels worth.
- You can actually achieve the results you want in a single worksheet, where you use an IF or CASE statement based on the parameter to return the different values, and have the calculations on the Level of Detail shelf so the table calculation settings are preserved. Here's what I mean:
IF [YTD or MONTH] = "YTD" THEN [Rank Sales Running Total] ELSE [Rank Sales] END
I am afraid it is not possible, because each needs different partitioning. This is the main reason for creating this idea. See what I mean in print screens below:
>>>Actually, it is possible. See the "All In One" worksheet in the attached. If you put both table calculations inside a third calculation like the above, then you can set a nested Compute Using for each table calculation. In addition, an additional calculated field is needed for the text for the table, and that uses a nested Compute Using as well. With this kind of setup, you ignore the Compute Using of the parent calc, because it is only the nested Compute Usings of the child calcs that actually do any computation. You set up nested Compute Using by choosing the Calculated Field drop-down prior to setting the Compute Using for each of the calcs within a nested table calc:
If you wanted to make this even simpler, you could have the IF statement actually perform one of the calculations and return the other, so you'd only see two calcs in the nested Compute Using instead of one.
Also, note that Tableau is actually returning two sets of "Best," "Upper," etc., one from each table calc, so you'll need to set the colors twice.
- Aggregated calculations that are in the view can already be used to partition table calculations, but not addressing - you need to uncheck the Ignore in Table Calculations option on the pill in the view to make them available for partitioning.
This comment gives me some hope it is possible to have dynamic partitioning/addressing in a single worksheet. I hope you can demonstrate it using the attached workbook.
>>> I set up an example of this in the "Partitioning on Measure" view. I added AVG(Profit) to the view, set it to discrete, and unchecked Ignore in Table Calculations. This adds the AVG([Profit]) to the partitioning, but the only place you'll see it is in the Edit Table Calc dialog:
The table calc is INDEX(), and it is addressing (Compute Using) on Order Date. Since there are 24 different values of AVG(Sales), one for each AVG(Sales) for each combination of Product Date & Category, there are 24 partitions with 24 1's being displayed in the view.
Thank you. I am amazed! I wish I could give you 10 x 4 points for correct answer
This is exactly what I want, and works precisely as I want in my production dashboard.
If I knew what I know now, I would not have written the idea regarding "dynamic table partitioning".
Hi Jonathan, I still feel joy of implementing your solution last night.
Even so, there is still one outstanding issue regarding median for even number of rows in partition. It was concluded as more or less impossible to do in this discussion:
To lessen the risk of again being premature with an idea, I hope to hear your opinion of adding Calculated Fields with Aggregations to this dialog:
- Edit Table Calculation > Compute using > Advanced > Order Along e.g. [Sales Running Total]
This is way late, this got a lot easier with the RANK calculations in v8 because they effectively sort the partition by the measure that they are using:
In this workbook there's a running sum using Table Across, then a rank percentile that is using Table Down, and finally a calculated field to generate the quintile labels & colors.
The outstanding issue was to identify medium for equal number of rows (years), that is, color two middle values as medium. I am afraid attached workbook doesn't address that. This is what my definition was:
- Highest Single value (highest)
- Upper Multiple values between medium and highest
- Medium Single value when 9 years; Two values when 10 years
- Lower Multiple values between medium and lowest
- Lowest Single value (lowest)
That said, I am confident that a combination of RANK_MODIFIED() and SIZE() would make this relative easy to do as you already have pointed out.
1 of 1 people found this helpful
Sorry I missed that. Yes, using the RANK calcs would definitely let you get the desired results. One thing to keep in mind about RANK vis-a-vis other calcs is that SIZE() counts every address in the partition whereas RANK(), RANK_MODIFIED(), etc. are counting non-Null values of the given measure. So there are cases (particularly when Tableau densification is involved) that the maximum of a RANK will be less than the value of SIZE(). In that case, WINDOW_MAX(RANK()) is a better calc to use in place of SIZE().