KNOWLEDGE BASE

Unable to Set Default Sort Order for Each Dimension in a Calculation that Swaps Dimensions


Published: 19 Nov 2016
Last Modified Date: 21 Nov 2016

Issue

Unable to set default sort order for each dimension in a calculation that swaps dimensions.

Environment

Tableau Desktop

Resolution

Step 1: Create a Parameter to control the view

  1. In the Dimension pane, click the arrow and select Create parameter...
  2. In the Create Parameter dialog box, do the following, and then click OK:
    1. Name the parameter "Dimension Swap".
    2. In Data Type, select "String".
    3. In Allowable values, select "List". 
    4. In the list of values, add the dimensions you will wish to swap. In this exampe: 
      1. Category > Category
      2. Segment > Segment
      3. Ship Mode > Ship Mode

Step 2: Create a calculation to use to chose the dimension

  1. Select Analysis > Create Calculated Field.
  2. In the Calculated Field dialog box that opens, do the following and then click OK:
  3. Name the calculated field. In the example workbook, the calculated field is named "Chosen Dimension".
  4. In the formula field, create a calculated field similar to the following:
    CASE [Dimension Swap]
    WHEN "Category" THEN [Category]
    WHEN "Segment" THEN [Segment]
    WHEN "Ship Mode" THEN [Ship Mode]
    END
    

Step 3: Create a calculation to use to set the sort order

  1. Select Analysis > Create Calculated Field.
  2. In the Calculated Field dialog box that opens, do the following and then click OK:
  3. Name the calculated field. In the example workbook, the calculated field is named Sort Order.
  4. In the formula field, create a calculated field similar to the following:
    	IF [Chosen Dimension] = "Same Day"
    	OR [Chosen Dimension] = "Home Office"
    	OR [Chosen Dimension] = "Technology"
    	THEN 1
    	ELSEIF [Chosen Dimension] = "First Class"
    	OR [Chosen Dimension] = "Consumer"
    	OR [Chosen Dimension] = "Office Supplies"
    	THEN 2
    	ELSEIF [Chosen Dimension] = "Second Class"
    	OR [Chosen Dimension] = "Happiness"
    	OR [Chosen Dimension] = "Corporate"
    	THEN 3
    	ELSE 4
    	END
    

Step 4: Build the View: 

  1. Add the  newly created Calculated Field "Chosen Dimension" to Rows.
  2. Add the newly created Calculated Field "Sort Order" to Text in the Marks card.
    • Right click SUM(Sort Order) and select Measure (Sum) > Minimum
  3. Right click [Chosen Dimension] on the Rows shelf and select Sort…
  4. In the Sort dialog, do the following and click OK:
    1. For Sort order, select Ascending.
    2. For Sort by, select Field.
    3. In the drop down menus, select Sort Order and Minimum.
    4. Click OK.
  5. Right click the parameter "Dimension Swap" and select Show Parameter Control.

Cause

A separate calculated field that assigns numerical values to each dimension member for sorting purposes must be created.
Did this article resolve the issue?