Last Modified Date: 20 Jul 2023
Environment
Tableau DesktopAnswer
The below steps are based on Sample - Superstore data.CLICK TO EXPAND SOLUTION
Parameter, Calculation and View creations step
Step 1: Create a parameter
- 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"
Step 2: Create a calculated field
- 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
Step 3: Build your view
- Right-click and drag [Order Date] to the Rows shelf
- In the Drop Field dialog, select MY(Order Date) and click OK
- Drag Region to the Rows shelf after MY(Order Date)
- Drag [Sales] to the Columns shelf
- Drag [Sort By] to the Rows shelf, placing it in the left most position
- 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
- CASE [Sort By]
- 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.
Thank you for providing your feedback on the effectiveness of the article.
Open new Case
Continue Searching
Knowledge Base
Community
Product Help
Training and Tutorials