Creating a Parameter Control to Sort the View

Published: 07 Dec 2016
Last Modified Date: 20 Jul 2023

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

Tableau Desktop

The below steps are based on Sample - Superstore data.

CLICK TO EXPAND SOLUTION

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: "Region", "Sales", "Month", "Month, Sales".
Right-click the [Sort By] parameter in the ata pane and select Show Parameter Control.
Create a second parameter called [Sort Order] with the values "asc" and "desc"

Select Analysis > Create Calculated Field.
Name the field Sort by Dimension, enter the following formula, then click OK:
CASE [Sort By] WHEN 'Sales' THEN RANK(SUM([Sales]), [Sort Order]) WHEN 'Month' THEN RANK(MIN( INT( DATETRUNC('month', [Order Date]) )), [Sort Order]) WHEN 'Region' THEN RANK(MIN([Region]), [Sort Order])
WHEN 'Month, Sales' THEN ( RANK(MIN( INT( DATETRUNC('month', [Order Date]) )), [Sort Order]) * 100000 ) + RANK(SUM([Sales]), [Sort Order]) END

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. This is why [Sort By] must be in the first position on the Rows shelf.
The RANK() function is a table calculation, which can be customized by changing how it is computed. For more information, please see Transform Values with Table Calculations
If sort dimensions are not added to the view, then the sort may not work as expected. If the example view only had [Region] then sorting by "Month" would not appear to do everything because every region has the same months.
If desired, you can create a calculated field that will change the granularity in the view. Something like:
- CASE [Sort By]
  WHEN 'Region' THEN [Region]
  WHEN 'Month' THEN STR(DATETRUNC('month', [Order Date]))
  ELSE ' '
  END
Dates need to be converted to an integer using INT() so that dates will retain chronological order
"Month, Sales" is a nested sort. In general to create nested sorts, multiply the rank of the first sort by a large number and then add the rank of the second sort. The number should be large enough that the ranks 'stay separate' within the result. For example, adding together rank 1 and rank 3 should look like 103 rather than 4. This allows reach dimension/measure to be sorted separately in the final view.