KNOWLEDGE BASE

Creating a Parameter Control to Sort the View


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

Question

How to use a parameter to choose which field 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
  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
 

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

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