KNOWLEDGE BASE

Nested Sorting with Parameter Control to Sort All Rows In Crosstab


Published: 11 Dec 2014
Last Modified Date: 07 Feb 2017

Issue

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

Environment

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?