Using the same example as above, you can now change it to a nested sort to see the products listed in order of sales within each region. The first thing we do is create a set that has a row for all Region/Product combinations. You can create a set by one of these methods:
-
select all marks in the view, right-click, and select Create Set from the context menu
-
select all marks in the view, point to one, and click the Create Set icon on the tooltip
In the Create Set dialog box, give the set a name. You can see that the set is made up of 68 total members, which are all possible combinations of Region and Category values. When finished, click OK.
The new set appears in the Sets area of the Data window.
Drag the new set and place it on the Rows shelf between the Region and Category fields. You'll see all the members of the set listed as Rows in the view.
Now instead of sorting the Category field, you sort the set. You can do that by opening the field menu for the set on the Rows shelf and selecting Sort. In the Sort dialog box, define how you want to sort the products. In this case we'll sort in Descending order based on the Sales field aggregated as a Sum. When finished, click OK.
You'll see that now you have a nested sort where the products are listed in order of sales within each region. The last thing you want to do is hide those set values so that you don't see the products and regions listed twice. You can do that by opening the field menu for the set field on the Rows shelf and clearing the selection of Show Header.
The final view is shown below. You can see that Chairs & Chairmats are the third highest selling products in the Eastern region but in the West, Tables are the third best selling product.
Note: 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.
