Tableau Padawan: Faster Nested Sorts

Tableau's sorting behavior is somewhere between "a little weird" and "WTF?!?" for those of used to the nested sorting behavior we can get in Excel and other tools. This is one case where Tableau's "mental model" of sorting doesn't match the mental model of many users, if you'd like Tableau to change this you can vote for the Independent Sorting feature request. Explaining why Tableau sorts the way it does would take a much longer post, for now I'm going to skip the "why" for the "how" and the "what" and jump into my preferred & faster way to do nested sorting using an ad-hoc calculation. First, a quick review of the existing methods:

The old-school method is to use a Tableau combined field as documented in the Tableau knowledge base article on nested sorting. The second method is to use a table calculation such as INDEX() or one of the RANK table calculations, Andy Kriebel did a nice tip on that. A challenge with the combined field is the number of steps involved, and the challenge using a table calculation is getting the compute using right and how the sorting breaks as we add or remove dimensions (see the comments on Andy's post for some examples).

Faster Nested Sorts using an Ad-Hoc Calculation

Tableau v9.0 introduced ad-hoc calculations aka type-in calculations where we can double-click on a shelf and start typing. Here's how to use an ad-hoc calculation to get a nested sort assuming you already have a continuous (green) pill in the view that you want to sort, such as the SUM(Sales) pill in the view on Columns below where we want to sort each Product Type within each Market:

1. Ctrl+drag (or Cmd+drag on the Mac) the continuous pill from Rows or Columns to the Level of Detail shelf. This creates a copy of the pill.***

2. Double-click on the pill you just copied on the Detail shelf and add a minus (-) sign to the beginning, then press Enter to save. Tableau changes it to say AGG(-SUM([Sales])). Congratulations, you just created your first ad-hoc calculation!
3. Right-click on the pill and change it to Discrete. It will now be a blue pill.

4. Drag the pill from the Detail Shelf to the Rows Shelf to be just before the pill you want to have a nested sort - Product Type in this case. Tableau will now show the view with the desired nested sort.
5. Right-click on the pill and turn off Show Header so the header doesn't show.

6. Right-click on the pill once more and turn off Include in Tooltip since we don't need it there either. Here's the resulting view:

*** If you're familiar with Tableau you might wonder why I don't just drag the pill directly to the Shelf involved and combine steps 1 & 4...the reason why is speed: Duplicating a continuous pill from Rows or Columns to the other shelf will cause Tableau to render a scatterplot and if there are a lot of marks this can take a lot of time for Tableau to draw the marks. Doing the manipulation of the pill on the Detail Shelf avoids that complication. (Joshua Milligan did an earlier version of this tip and you can see the scatterplot appearing in his GIF, check out that post for other great tips on ad hoc calcs).

How the Sort Works

The magic here is that instead of trying to create a dimension that we can sort via a measure (the combined field method) or create a table calculation that requires just the right compute using (the table calc method) we are just using the value of measure as a discrete pill and taking advantage of Tableau's default sort of discrete pills that sorts values in alphanumeric ascending order. The minus sign used in step #2 simply makes big numbers into small ones so they sort first alphanumerically and get us an effective descending sort, if you really want an ascending sort then you can skip step #2.

Why This is So Great #1 - Adding/Removing Dimensions

A challenge of the other two methods is that if we add or remove dimension pills from the view then we need to change the combined field or table calculation compute using, when using a discrete regular aggregate measure for the sort all we need to do is rearrange pills. For example if I add State to the view now I have a nested sort on Product Type & State:

If I move the AGG(-SUM([Sales])) pill to be in-between Product Type & State then I get a nested sort of State within each Product Type:

When the pill we are getting a nested sort is based on a table calculation we still need to pay attention when adding or removing dimensions because that is the nature of table calculations (dimensions added to the view are automatically added to the partitioning of the table calculation). There's still an advantage for table calculations, tho:

Why This is So Great #2 - Table Calculations

The combined field method of nested sorts doesn't work with table calculations because Tableau doesn't support sorting a dimension (the combined field) by a table calculation. The second method of an INDEX() or a RANK table calculation to get a nested sort adds anywhere from a little to a lot of complexity to the view. Using just the table calculation pill as an ad-hoc calculation gets around those limitations. Conclusion

This is an example where even if Tableau isn't providing us the controls that we might want (such as Excel-style nested sorts) the fact that Tableau is a data-driven drawing engine is such that we can get the desired layout by setting up the data, in this case by using an ad hoc calculation.

Here's a link to the nested sort with ad-hoc calculation workbook on Tableau Public.