Introduction

Andy tasked us with our first homework on the second day of discovering as many ways to create nested sorting as possible.
What exactly is nested sorting? Nested sorting is sorting by a measure within another measure. So in Tableau, this is would be to sort the categories within each pane.

I think I have boiled down all the methods I have found up until now into 4 main methods and 1 miscellaneous section:
1. Sorting on a combined field
2. Rank
3. Table calculation with custom sort (Index, First, Running Total, anything really)
4. Negative discrete measures (anything with the same ranking as the measure, Window Sum, Percentage Total, etc)

In my particular case we used region and category as the dimensions and sum(sales) as the measure we were sorting on (descending).

A link to the workbook including the 4 methods (and one crazy calculation) can be found here:
https://public.tableau.com/views/NestedSorting_4/Method1CombinedField

Methods

1. Sorting on a combined field

I have to admit I was super rusty on this despite having learned it in the past. I have never incorporated this in my work so far and had only learned it when being taught once upon a time. This seems like the “truest” method of nested sorting.
i. Create a combined field with Region and Category.
   Do this by clicking Region and Category with control (Ctrl) held -> right click->create-> Combined Field.
ii. Drag this before Category
iii. Sort the combined field by the Sum(Sales)
   Right-click the combined field and sort descending by Sales at the aggregation level of Sum.

This is the only method which can be switched from descending to ascending at the axis icon.

2. Rank

I came across this method in Andy’s blog when searching for methods. This was the most online help gave me before I started experimenting with Tableau.
(http://www.vizwiz.com/2015/03/nestedsort.html)

i. Duplicate measure
Click measure whilst holding control (Ctrl) and drop into either the rows or columns shelf.
ii. Create a quick table calculation of rank
Right-click the measure on the shelf and select table calculation->rank.
iii. Convert this to discrete.
Right-click the measure on the shelf and select discrete.
iv. Drag the new field before category.

This should not require any formatting for the table calculation.

3. Table calculation with custom sorting

This one is just slightly longer than the method above and utilises table calculation’s custom sorting feature.

i. Create a table calculation
Create a calculated field with index() inside.
ii. Convert index calculation to discrete
     Right-click the index measure and select convert to discrete.
iii. Drag the Index calculation before Category
iv. Edit the index table calculation to custom sort.
      Right-click edit table calculation->specific dimensions -> select all-> custom sort->sum(sales)->descending order. (here you definitely need a screenshot)

4. Negative/Positive discrete calculations

Basically, any calculation which retains the same rank as the value we are sorting can be used for this but the simplest is our Sum(Sales) which we are sorting on. For this, all we do is:

i. Duplicate Sum(Sales) on the shelf.
Click measure whilst holding control (Ctrl) and drop into either the rows or columns shelf.
ii. Edit in shelf to add a minus in front. (this is called creating an ad-hoc calculation, maybe link to the KB article)
Double click on the measure in shelf to enable editing in shelf.
iii. Convert this field to discrete.
Right-click the measure and select discrete.
iv. Drag between Region and Category.

This method requires the least amount of time, alongside method 2 to create a nested sort; it was, in fact, the last method I figured out, despite how simple it is.

Why Do These Methods Work?

Let me just explain a little bit about what the magic sauce is behind methods 3 and 4 working. I heavily abuse the ability to convert measures into discrete fields. This means that we can place them before category and it will automatically sort the categories according to the measure’s values. These values have been manipulated in some form for each of the methods to be the correct value to sort. Method 2 abuses the custom sort of table calculations in combination with making it discrete and placing it before category means that it will sort between dimensions.

Hope you have enjoyed this blog post, I certainly enjoyed making it :).