KNOWLEDGE BASE

Creating a Parameter Control to Sort By Dimension


Published: 07 Dec 2016
Last Modified Date: 03 Jan 2019

Question

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

Environment

Tableau Desktop

Answer

CLICK TO EXPAND STEPS
The below steps are based on Sample - Superstore data. 

Step 1: Create a parameter

  1. Right-click in the Data pane and select Create Parameter. 
  2. 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.  
  3. Right-click the Sort By parameter and select Show Parameter Control. 

Step 2: Create a calculated field 

  1. Select Analysis > Create Calculated Field.
  2. 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 

  1. Drag Product Name, Sub-Category, Category, and Region to Rows. 
  2. Drag Sales to Columns. 
  3. Drag Sort by Dimension to Rows, placing it to the  left of Product Name. 
  4. Right-click Sort By Dimension on Rows, and select Sort. Choose the following options, then click OK: 
    • Sort order: Ascending
    • Sort by: Alphabetic
  5. 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.
 
CLICK TO EXPAND STEPS
Note on Mixing Data Types
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.

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.

Discuss this article... Feedback Forum
Did this article resolve the issue?