KNOWLEDGE BASE

## Nested Sorting with Parameter Control to Sort All Rows In Crosstab

Published: 11 Dec 2014
Last Modified Date: 09 Aug 2017

### Issue

How to sort all rows when using a crosstab view with multiple dimensions.

Tableau Desktop

### Resolution

Use nested sorting in combination with a parameter and a dynamic measure to choose which field will sort the combined field.
Note: See the attached workbook for a demonstration of these instructions.

### Step 1: Build underlying view

• Drag the desired dimensions and measures into the view.
• In the Dimensions pane, Ctrl+Click on all dimensions that are in the view. Right-click on one of the selected fields and choose Create > Combined Field.
• Drag the combined field to the Rows shelf, placing it to the left of the other dimensions.
• Right-click on the combined field on the Rows shelf and clear the selection for Show Header.

### Step 2: Create and show parameters

1. Create "Sort by" parameter:
• Right-click on the combined field created above and select Create Parameter.
• Name the parameter and in the Properties select String from the Data Type drop-down list.
• In Allowable values select List, and then enter into the List of values all dimensions desired for sorting.
1. Create "Sort Order" parameter
• Right-click on the combined field created above and select Create Parameter.
• Name the parameter and in the Properties select String from the Data Type drop-down list.
• In Allowable values select List, and then enter "Ascending" and "Descending" into the List of values.

### Step 3: Create "Sort" calculations for all Dimensions

For example:
```// Returns an ordinal number of the first 4 characters
// Purpose: create a number to sort the dimension ascending or descending
// Please be advised that the sort order will only consider the first 4 characters of the field as written here (this can be expanded by adding zeros to the multiplier and adding new rows referring to 7, 8, etc.)
ZN(ASCII(LEFT([Region],1)))*10000000000 +
ZN(ASCII(MID([Region],2,1)))*100000000 +
ZN(ASCII(MID([Region],3,1)))*1000000 +
ZN(ASCII(MID([Region],4,1)))*10000 +
ZN(ASCII(MID([Region],5,1)))*100 +
ZN(ASCII(MID([Region],6,1)))```

### Step 4: Create "Sort Measure" calculation

For example:
```CASE [Sort by]
WHEN 'Sales' then
IF [Sort Order] = "Ascending" THEN SUM([Sales]) ELSE -SUM([Sales]) END
WHEN 'Region' THEN
IF [Sort Order] = "Ascending" THEN AVG([Sort Calculation - Region]) ELSE -AVG([Sort Calculation - Region]) END
END```

### Step 5: Sort combined field by Sort Measure

• Right-click on the combined field on Rows shelf and select Sort.
• Under Sort by select Field > Sort Measure

### Cause

Dimensions on the Rows or Columns shelf are treated hierarchically, so sorting on a dimension will not affect any dimensions left of it on the shelf.
Did this article resolve the issue?