Last Modified Date: 21 Aug 2013
Suppose you are looking at the sales categories for all regions. Have you noticed that if you sort the list of categories to show the highest sales at the top, the products are listed based on their total sales, across all regions?
In the view above, you can see that the Central region sells more products in the Telephones and Communications category than in the Chairs & Chairmats category, even though across all regions, the Chairs & Chairmats category sales are better. This kind of sorting is good because you can see that although, as a whole, Chairs & Chairmats are selling better than products in the Telephones and Communications category, that is not the case in the Central region.
However, you may want to sort the products within each region instead. You can do that with nested sorting. Using the same example as above, you can now change it to a nested sort to see the categories listed in order of sales within each region. The first thing to do is create a combined field that has a row for all Region and Category field combinations.
In the Dimensions pane, press the CTRL key and select the dimensions for which you want to compute the Top N. In this example, select the Region and Category fields. Then do one of the following:
- For Tableau Desktop 8.0 and later, right-click one of the selected fields and select Combine Fields. The new combined field displays at the top of the Dimensions pane.
- For Tableau Desktop 7.0 and earlier, right-click one of the selected fields and select Create Set. In the Create Set From Selection dialog box, give the set a name, and then click OK. The new set displays in the Sets pane.
Replace the Category field with the new combined field on the Rows shelf. Now instead of sorting the Category field, you sort the combined field. You can do this sort by right-clicking the Category & Region field in the Rows shelf and selecting Sort. In the Sort dialog box, define how you want to sort the products. In this case, sort in Descending order based on the Sales field with an aggregation as a Sum. When finished, click OK.
You'll see that now you have a nested sort where the categories are listed in order of sales within each region. The final view is shown below. You can see that Chairs & Chairmats are the second highest selling category in the Central region but in the East, Telephones and Communications are the second best selling category.
Note: For 7.0 and earlier, you may also want to change the formatting so that the row dividers are at the Region level instead of on every row. Select Format > Borders to open the Format window.
Alternate Search Terms: sorting, sort, top n, nested, sort inside a group