Creating a Parameter Control to Sort By Dimension

Published: 07 Dec 2016
Last modified date: 20 Jul 2023

Question

How to use a parameter to choose which dimension to sort your view by.

Environment

Tableau Desktop

Answer

The below steps are based on Sample - Superstore data.

CLICK TO EXPAND SOLUTION

Parameter, Calculation and View creations step

Step 1: Create a parameter

Right-click in the Data pane and select Create Parameter.
In the Create Parameter dialog, do the following, then click OK:
- Name: Sort By
- Data Type: String
- Allowable Values: List
- For Value, enter the following list: Category, Sub-Category, Product Name and Region.
Right-click the Sort By parameter and select Show Parameter Control.

Step 2: Create a calculated field

Select Analysis > Create Calculated Field.
Name the field Sort by Dimension, enter the following formula, then click OK:
CASE [Sort By] WHEN 'Category' THEN [Category] WHEN 'Sub-Category' THEN [Sub-Category] WHEN 'Product Name' THEN [Product Name] WHEN 'Region' THEN [Region] END

Step 3: Build your view

Drag Product Name, Sub-Category, Category, and Region to Rows.
Drag Sales to Columns.
Drag Sort by Dimension to Rows, placing it to the left of Product Name.
Right-click Sort By Dimension on Rows, and select Sort. Choose the following options, then click OK:
- Sort order: Ascending
- Sort by: Alphabetic
Right click Sort by Dimension on Rows, then un-check Show Header.

You can now use the Sort By parameter control to select which dimension to sort by.

To view the steps showed in the below video, please expand the above section. Note: the video has no sound.

Additional information

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.

If you want to mix data types or add an 'Ascending or Descending' parameter to the sort, a RANK() can be added to any non-number fields. See the attached "Sorting by ANYTHING" workbook.

Use a calculation similar to the following instead of the calculation in the above instructions:

IF [Ascending/Descending]='Ascending' then 1 else -1 END
*
IF [Sort by]='Category' then -rank(min([Category]))
elseif [Sort by]='Sub-Category' then -rank(min([Sub-Category]))
elseif [Sort by]='Sales' then SUM([Sales])
elseif [Sort by]='Profit' then SUM([Profit])
END

RANK() can be used on any data type to convert it to a number and retain the original order. For 'Descending', the entire calculation can be multiplied by -1.