KNOWLEDGE BASE

## Creating a Parameter Control to Sort the View

Published: 07 Dec 2016

### Question

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

### Environment

Tableau Desktop

The below steps are based on Sample - Superstore data.

CLICK TO EXPAND SOLUTION

### Parameter, Calculation and View creations step

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

### 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 '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`

### Step 3: Build your view

1. Right-click and drag [Order Date] to the Rows shelf
2. In the Drop Field dialog, select MY(Order Date) and click OK
3. Drag Region to the Rows shelf after MY(Order Date)
4. Drag [Sales] to the Columns shelf
5. Drag [Sort By] to the Rows shelf, placing it in the left most position
6. Right-click [Sort By] on the Rows shelf and uncheck Show header

• 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.