Do I Sort on "Field" or "Nested"

    

In our recent Tableau Bootcamp, Ken was showing some sorting options when a client, who had a good bit of Tableau experience, asked, "when sorting, I always chose 'Nested', but you chose to sort on a 'Field' - what's the difference?" Honestly, neither of us really knew. Dangit. I really hate when I get stumped and Ken does too...and he stumped us both! We told him we would look into it and get back to him...and Ken pushed forward with the class. Of course, it was driving me crazy so I looked it up. Tableau's knowledge base site defines Field sort and a Nested sort as follows:

Field: lets you specify the field value used to determine the sort order. The field doesn’t need to be used in the visualization. You can also select an aggregation for the sorting field. The aggregation options available depend on the field type.

Nested: lets you select the field value used to determine the sort order. The field doesn’t need to be used in the visualization. You can also select an aggregation for the sorting field. The aggregation options available depend on the field type.

Take a good look at those definitions. Yep...that's right!  They are exactly the same! Not a single difference. So, I decided to start investigating this myself. 

I started by building a simple bar chart of Sales by Category and I highlighted Office Supplies just so I could "keep an eye on it". The chart looks like the following (and you can see all of these charts on my Tableau Public workbook): 

I did not sort this in any way, so Tableau defaulted to sorting it in alphabetical order.

Next, I was curious what would happen if I clicked on the axis to perform the sort. Would Tableau use a Field or Nested sort?  Below is a gif to show you what happened:

As you can see, it defaulted to a Nested sort. I was actually kind of surprised because I tend to use a Field sort. Interesting, but let's move on.

I copied the original, unsorted chart to a new worksheet then set the sort myself. I sorted the using Field (descending on Sum of Sales). Below is what I did:

The result of setting the sort on Field was as follows:

As you can see, it sorted the bar chart in descending order of sum of sales, just as you'd expect. So instead of a sort using Field, let's do the same thing using Nested: 

And the results:

Again, this sorted the bar chart in descending order of sum of sales, just as you'd expect. But that's exactly what the Field sort did, right?  Let's make sure by looking at both of them side by side. 

Yep, both are exactly the same, sorted in descending order of sales.  I'm starting to think that the Tableau knowledge base article was onto to something.

But as "nested" would imply, Ken and I both figured that the Nested option would be impacted by multiple dimensions on the view.  So I added Ship Mode to the Rows shelf in front of Category. For this, I just used the default sort (alphabetical):

In this case, we can see that both the ship modes and the categories are sorted in alphabetical order - the highlighted Office Supplies is always second in each group.

So let's apply a Field sort on the Category pill as shown below:

So we sorted on Field, descending by Sum of Sales. So let's look at the result:

So here, we see it's sorted in descending order by sum of sales, right?  Well...I'm not sure. Let's first look at the First Class ship mode. 

Yep, the categories are, in fact, sorted in descending order by sum of sales where Office Supplies is the smallest value. Okay, let's check the next one, Same Day.

Same thing!  Office Supplies is the smallest value. Looking promising!  Now the next ship mode:

Uh oh!  I thought we were sorting in descending order of sum of sales, but this one appears to be sorted in Ascending order. In this case, Office Supplies is the largest value yet it is shown last (where in other ship modes, the largest value was shown first). What the heck is happening?  Before we investigate this problem further, let's see what happens with the Nested sort. Below are the results:

Well, this one is different. For First Class, we see Office Supplies is the smallest and it is last. And we see the same thing for Same Day. But Second Class is different than the Field sort. Office Supplies is the largest value and it appears first. With the Nested sort on Category, the categories are, in fact, always sorted as we selected - descending order of sales.

So let's compare Field Sort and Nested Sort when using multiple dimensions:

So what is happening here?  Well, the Nested sort on Category (right side) seems quite obvious. Within each ship mode, it is sorting the categories in descending order of sales. But the left side is a bit curious. For three ship modes, it appears to be sorting the categories in descending order and one of them in ascending order (Second Class). Well, what is is actually doing is sorting the categories based on their total sales across all ship modes. Remember our first example when we did not have ship mode?

Office Supplies is the smallest value.  Likewise, with our Field sort on Category with Ship Mode first, we see that Office Supplies is ALWAYS last.

So the answer to the question...well, if there is only one dimension, then the Field sort and the Nested sort are, in fact, identical and it doesn't matter which one you select. However, when you start introducing additional dimensions, it matters. A Field sort will sort based on the aggregate and not take into account the other dimensions on the view while a Nested sort will, in fact, take those other dimensions into account.

I hope this was helpful...I know it was for me! Thanks for reading.

───────────────────────

Need help with anything related to Tableau?  Through Moxy Analytics, Ken and I provide consulting services such as Tableau Lifeline (get us for 1 hour to help solve a sticky problem), Fractional Data Hero (get us on your team for N number of hours a month for whatever you want us for), Tableau Training, and of course, project work. Click the Icon below if you are interested.

Kevin Flerlage, April 1, 2024

Twitter | LinkedIn | Tableau Public