KNOWLEDGE BASE

Creating a Parameter Control to Sort By Dimension


Published: 07 Dec 2016
Last Modified Date: 17 Aug 2018

Question

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

Environment

Tableau Desktop

Answer

CLICK TO EXPAND STEPS
Option 1
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
Option 2
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
 
CLICK TO EXPAND STEPS
Option 3
The following instructions can be reviewed in the workbook attached below.

Step 1: Create a parameter

  1. Right-click in the parameter pane and select Create Parameter.
  2. Name the parameter Sort By.
  3. From Data Type, select String.
  4. For Allowable values, select List
  5. Populate the value list with measure/dimension names (ex: "Sales", "Region", etc.).
  6. Click OK.

Step 2: Create a calculated field to use for the sort

  1. Navigate to Analysis > Create calculated field....
  2. Name the calculated field Sort Control and enter a formula similar to the following:
    CASE [Sort By] 
    When "Sales" Then [Sales] 
    When "Region" Then [Region] 
    END
  3. Right-click on the parameter from Step 1 and select Show parameter control
  4. Right-click on the dimension to be sorted by selected measure, and select Sort....
  5. For Sort Order, select Descending.
  6. For Sort By, select Field.
  7. From the drop-down menu, select the parameter created in Step 1. 
  8. Navigate to Analysis > Create calculated field....

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.
Did this article resolve the issue?