Using the same example as above, we'll 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 selecting two fields in the Data window (hold down the Ctrl key), right-clicking and selecting Create Set.
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 Product values. When finished, click OK.
The new set displays in the Sets area of the Data window.
Drag the new set and place it on the Rows shelf between the Region and Product - Subcategory fields. You'll see all the members of the set listed as Rows in the view.
Now instead of sorting the Product 2 - Subcategory field, 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.
