3 of 3 people found this helpful
An INDEX with an Advanced Compute using on the sorting dimension(s) and a chosen sort measure will do the job without a combined field. The Advanced Compute Using internally creates a "combined field" that is then sorted by the chosen measure. That INDEX calc would then be placed as a discrete measure onto the Rows Shelf.
Thanks Jonathan. I haven't thought that way.
I am trying what you have suggested & got the correct answer.
So, sharing the steps -
1) Create a calculated field using Index()
2) Right click on this index function calculated field & make it discrete
3) Put this field in between the two dimensions.
4) Go to advanced option of this function
5) Put the field like below -
Here First put the dimension which you are using at the lowest level like I have used continents first.
Also at the bottom section use Sales with sum in descending order.
6) You can choose At the level as "Container" & Restarting at "Continent" (But This will not affect the view)
I think process is correct. Am I right Jonathan?
Nested Sorting using Index.twbx 970.1 KB
6 of 6 people found this helpful
Thanks for doing this! The instructions are essentially there, here's a quick rewrite to include details for people who aren't familiar with table calcs. I also took out step #6 as the settings are unecessary and if implemented the wrong way (which I've seen to be pretty common with new users who are still getting used to navigating around these not-very-user-friendly dialogs) will lead to incorrect results and/or frustrations.
1) Create a calculated field using the formula INDEX() and save it.
2) In the Measures window, right click on this Index calculated field & choose "Convert to discrete."
3) Drag the Index field on to the Rows Shelf between the two dimensions. Tableau adds the Index to the view.
4) Right-click on the Index pill you just added and choose Edit Table Calculation... The Table Calculation dialog appears.
5) In the Compute Using drop-down, choose the Advanced... option. The advanced dialog appears.
6) Use the >,<,Up, and Down buttons to move the dimensions from Partitioning into Addressing in the exact order that you have them on the Rows Shelf so the left-right order in rows is reflected in a top-bottom order in the Addressing window, in the case of your original screenshot it would be Region, Department.
7. In the Sort area, click the Field button to set up a custom sort on Sales/Sum/Descending. This will sort the combinations of distinct value(s) of the addressing dimensions by the SUM(Sales) measure, duplicating the effect of the combined field.
8. Click OK to close the Advanced dialog.
9. Click OK to close the Table Calculation dialog.
10. Click on the Index pill on Rows and uncheck Show Header to hide the Index column.
Here's a pic of the results:
Note that the Index results you'll see aren't 1,2,3,... for each region because we've set up the calc only have one partition (the entire data set), however because Region is in the view the values of index are still in order. If you want to see a 1,2,3, for each Index then in the Table Calculation dialog you can set Restarting Every to Region:
Also, I'm really used to first thinking of INDEX() because that's what was available when I started using Tableau. INDEX() doesn't support ties, though James Baker and Richard Leeke did some really nice work to make calcs that would do ranking & percentiles & support ties. Tableau 8.1 introduced several RANK functions that do support ties, and the RANK() function takes a measure as an argument so the easier option that I should have suggested first is:
1) Create a calculated field using the formula RANK(SUM([Sales])) and save it.
2) In the Measures window, right click on this Rank calculated field & choose "Convert to discrete."
3) Drag the Rank field on to the Rows Shelf between the two dimensions. Tableau adds the Rank to the view. The default Compute Using of the table calculation is Table (Down), which means that the view is automatically addressing on both Region & Department, and the default setting of RANK is to do a descending sort of the given measure, so it's already duplicating the Advanced Compute Using & custom sort of the Index instructions above. Here's the view:
4) Click on the Rank pill on Rows and uncheck Show Header to hide the Rank column.
The RANK() function is a competition rank, if you want to use a different kind of ranking then you can use another one of the functions. If you want the rank to be 1,2,3... for each Region then you can just set the Compute Using to Department using the right-click context menu on the pill.
I attached a Tableau 8.2 workbook showing the options.
1 of 1 people found this helpful
Perfect !! Thank you Jonathan !!
How can I use this scenario if i have one dimension and multiple measures?
Thanks & Regards,