
1. Re: Nested Sorting without Combined field
Prashant Sharma Jul 24, 2014 2:47 AM (in response to kazuya toida)Why you do not want to use combined field? Are you using hierarchies? I do not think that this is possible without combined fields.
Warm Regards,

2. Re: Nested Sorting without Combined field
Jonathan Drummey Jul 24, 2014 2:36 AM (in response to Prashant Sharma)3 of 3 people found this helpfulAn 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.
Jonathan

3. Re: Nested Sorting without Combined field
Prashant Sharma Jul 24, 2014 3:18 AM (in response to Jonathan Drummey)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?
Warm Regards,

Nested Sorting using Index.twbx 970.1 KB


4. Re: Nested Sorting without Combined field
Jonathan Drummey Jul 24, 2014 5:12 AM (in response to Prashant Sharma)6 of 6 people found this helpfulHi Prashant,
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 notveryuserfriendly 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) Rightclick on the Index pill you just added and choose Edit Table Calculation... The Table Calculation dialog appears.
5) In the Compute Using dropdown, 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 leftright order in rows is reflected in a topbottom 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 rightclick context menu on the pill.
I attached a Tableau 8.2 workbook showing the options.
Jonathan

5. Re: Nested Sorting without Combined field
Satheesh Kumar Kuna Jan 4, 2017 10:44 AM (in response to Jonathan Drummey)1 of 1 people found this helpfulPerfect !! Thank you Jonathan !!

6. Re: Nested Sorting without Combined field
Nandhakumar Ramanathan Oct 27, 2017 1:39 PM (in response to Jonathan Drummey)Hi Jonathan,
How can I use this scenario if i have one dimension and multiple measures?
Thanks & Regards,
Nandha

7. Re: Nested Sorting without Combined field
Nandhakumar Ramanathan Oct 30, 2017 1:48 PM (in response to Jonathan Drummey)Hi Jonathan,
Please let me know how can i use this logic in this scenario?
Want to get Top and Bottom N values in all measure columns.
Thanks & Regards,
Nandha

sample_All_Column_sort.twbx 1.0 MB
