Last Modified Date: 17 Aug 2020
AnswerThe 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])
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
- 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 ' '
- 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.
Discuss this article...
Thank you for providing your feedback on the effectiveness of the article.
Open new Case
Training and Tutorials