3 November 2014
Have you ever noticed that using the quick sort in Tableau sometimes produces unexpected results? Particularly when you have more than one Dimension in play?Tableau does not comply with 'traditional' sorting methods - the kind you might find in Microsoft Excel for example. This post is going to explore why Tableau sorts the way it does, and how we can get around some of the more complex sorting issues.
We can use a quick sort on either our State field, or our Profit field.
A quick sort on state sorts our bars ascending or descending on the state name.
Whereas a sort on profit will order our bars by the amount of profit.At any time we can bring up the Sort dialogue box (right-click on the Dimension field in the view and select Sort) to see what Tableau has applied to create the quick sort.
Above is how Tableau applied a quick sort by Profit. Note - you can go ahead and select any Measure field to sort by, even if it's not in play on the View.Quick sorts are great when we are creating quick explorations of our data and our charts are fairly simple. However, challenges arise when we start adding in additional fields to the view.
Hmm. Something's not right. In the Central region the Small Pack container is making less profit than Large Box, but the bar for Small Pack is above the bar for Large Box? Why does Tableau do this?Tableau is actually taking in to account the data across all Regions when applying this sort. Across all regions Small Pack accounts for $67,161 of profit, whereas Large Box accounts for less: $65,490. So Tableau is sorting the bars within our Regions with Small Pack above Large Box. While this type of sorting may be useful in certain scenarios, it's not what I'm looking for today.So - how do we get around this? In version 8 we can create a combined field. In version 7 we would have to create a set. This post will follow using the version 8 method, but for more on the version 7 method you can click here.
The combined field will then be found in the Dimensions pane. You can edit the field at any time by right-clicking. You can change the order of the fields, rename the field and set what character the members are separated by.
You can add however many fields to the combined field as needed. So if you need to sort based on three Dimensions, just multi-select all three before you create the combined field.Now we have our combined field, we can re-create the view using the combined field, rather than the Region and Container fields.
The last step is to re-create the sort on the new combined field.
Here's our final view:
Success!You can find out more about how Tableau sorts from these links:Jonathan Drummey's great Tableau WikiNested Sorting from Tableau's Knowledge Base
Quick Sort
The quick sort function in Tableau can be useful in most simple scenarios. For all my examples I'm going to be using Superstore Sales. In the image below I have a bar chart showing profit by state.We can use a quick sort on either our State field, or our Profit field.A quick sort on state sorts our bars ascending or descending on the state name.Whereas a sort on profit will order our bars by the amount of profit.At any time we can bring up the Sort dialogue box (right-click on the Dimension field in the view and select Sort) to see what Tableau has applied to create the quick sort.Above is how Tableau applied a quick sort by Profit. Note - you can go ahead and select any Measure field to sort by, even if it's not in play on the View.Quick sorts are great when we are creating quick explorations of our data and our charts are fairly simple. However, challenges arise when we start adding in additional fields to the view.Why is my sort broken?
Say I want to find out across regions and container types the amount of profit I make. I want to be able to sort my bars by profit within the different regions. So I use a quick sort on profit, here is what I get:Hmm. Something's not right. In the Central region the Small Pack container is making less profit than Large Box, but the bar for Small Pack is above the bar for Large Box? Why does Tableau do this?Tableau is actually taking in to account the data across all Regions when applying this sort. Across all regions Small Pack accounts for $67,161 of profit, whereas Large Box accounts for less: $65,490. So Tableau is sorting the bars within our Regions with Small Pack above Large Box. While this type of sorting may be useful in certain scenarios, it's not what I'm looking for today.So - how do we get around this? In version 8 we can create a combined field. In version 7 we would have to create a set. This post will follow using the version 8 method, but for more on the version 7 method you can click here.Combined Fields
To create a combined field multi-select (click while holding Ctrl) the Dimensions required in the view. In this case that's Container and Region. Then right-click one of the selected Dimensions and select Combine Fields.The combined field will then be found in the Dimensions pane. You can edit the field at any time by right-clicking. You can change the order of the fields, rename the field and set what character the members are separated by.You can add however many fields to the combined field as needed. So if you need to sort based on three Dimensions, just multi-select all three before you create the combined field.Now we have our combined field, we can re-create the view using the combined field, rather than the Region and Container fields.The last step is to re-create the sort on the new combined field.Here's our final view:Success!You can find out more about how Tableau sorts from these links:Jonathan Drummey's great Tableau WikiNested Sorting from Tableau's Knowledge Base