KNOWLEDGE BASE

Sorting Dimensions by Calculated Fields That Use Table Calculations


Published: 03 Jun 2014
Last Modified Date: 20 Jun 2017

Question

How to sort a dimension by a calculated field that uses a table calculation.

Environment

Tableau Desktop

Answer

The ability to sort a dimension by a calculated field that uses a table calculation is currently not built into the product. However both the following workarounds can be reviewed in the workbook "Sorting by Rank CAGR" attached to this article (located on the right side of this page). The workbook details how to use table calculations and LOD to rank members of a dimension for a specific year.

Option 1 - Use expressions and Compute Using options

Use expressions and Compute Using options to sort by calculated field. For more information about Table calculations, see the on demand video Modifying Table Calculations.

Step 1 - Create a Lookup calculated field

  1. Select Analysis > Create Calculated Field...
  2. In the Calculated Field dialog box that opens, do the following and then click OK:
    1. Name the calculated field. In the example workbook, the calculated field is named !CAGR lookup.
    2. In the formula field, create a calculated field similar to the following:
      lookup(POWER(ZN(SUM([Sales]))/ 
      LOOKUP(ZN(SUM([Sales])), FIRST()),ZN(1/(INDEX()-1))) - 1,last()) 

Step 2 - Create a Rank calculated field

  1. Select Analysis > Create Calculated Field...
  2. In the Calculated Field dialog box that opens, do the following and then click OK:
    1. Name the calculated field. In the example workbook, the calculated field is named !CAGR RANK.
    2. In the formula field, create a calculated field similar to the following:
      RANK([!CAGR lookup]) 
      

Step 3 - Create a Parameter

  1. Click the arrow next to the Dimensions panel and select Create a parameter.
  2. Name the parameter "TOP N".
  3. In the Data Type, choose Float.
  4. In Current Values, type 5.
  5. Click OK.

Step 4 - Create a Filtering calculated field

  1. Select Analysis > Create Calculated Field...
  2. In the Calculated Field dialog box that opens, do the following and then click OK:
    1. Name the calculated field. In the example workbook, the calculated field is named !Top N filter.
    2. In the formula field, create a calculated field similar to the following:
      IF [!CAGR RANK]<=[TOP N] THEN 'Include' 
      ELSE 'Exclude' END 

Step 5 - Build the View

  1. Drag "Order Date" to Columns.
  2. Drag "Sub-Category" to Rows.
  3. Drag "Sales" to the Text Cards.
  4. Right click "Sales" and select Compute Using > Compound Growth Rate.
  5. Right click "Sales" and select Format > Percentage > 0 decimal.
  6. Right click "!CAGR RANK" and select Convert to Discrete.
  7. Place "!CAGR RANK" on Rows, to the left of "Sub-Category".
  8. Right click "!CAGR RANK" in Rows and select Edit Table Calculation... and select Table(Down).
  9. Drag "!Top N" filter to Rows.
  10. Right click"!Top N", select Filter and choose Include to only select the TOP 5.
  11. Right click "!TOP N" in the Parameter pane and select Show Parameter Control.

Option 2 - Use level of detail expressions in place of table functions

As an alternative workaround, modify the calculated field to use level of detail expressions in place of table functions. For more information, see Level of detail expressions

Step 1 - Create the LOD calculated field

  1. Select Analysis > Create Calculated Field...
  2. In the Calculated Field dialog box that opens, do the following and then click OK:
    1. Name the calculated field. In the example workbook, the calculated field is named !LOD.
    2. In the formula field, create a calculated field similar to the following:
      power(
      SUM([Sales])
      /
      min({ EXCLUDE [Order Date]:
      (zn(sum(IF YEAR([Order Date])=
              year({ EXCLUDE [Order Date]:MIN([Order Date])})
       then [Sales] else 0 END)))})
      
      ,ZN(1/(YEAR(min([Order Date]))
              -YEAR(min({ EXCLUDE [Order Date]:MIN([Order Date])}))))
      )-1
      

Step 2 - Build the View

  1. Duplicate the Sheet from Option 1.
  2. Replace "Sales" from the Text cards by the newly created field "!LOD".
  3. Right click "!LOD" and select Format > Percentage > 0 decimal.

Additional Information

To view a Bottom N example, see this community question: https://community.tableau.com/message/218933#218933.

Please note that the ability to sort a dimension by a calculated field that uses a table calculation is currently not built into the product. 

To view the above steps in action see the video below: 
Did this article resolve the issue?