KNOWLEDGE BASE

Hiding Only the Blank Column when Using Difference Table Calculation


Published: 04 Feb 2013
Last Modified Date: 10 Dec 2019

Question

How to hide a blank column when using a Percent Difference or Difference table calculation without hiding the other measures in the view.

Environment

Tableau Desktop

Answer

CLICK TO EXPAND SOLUTION

Option 1: Manually hide the blank column

If there is more than one measure or dimension in the view, the blank column cannot be hidden without also hiding all instances of that Dimension value. For example, hiding one Q1 column will hide the Q1 column for all years. Use the following workaround if there is only a measure or a dimension in the view.
  1. Right-click the blank column header.
  2. Select Hide.
Note: This solution will hide every column with the same dimension value, for example all "2016" columns. To hide only one instance of a dimension value, for example only "Consumer 2016", see Hide Only One Instance of Sub-Category column
CLICK TO EXPAND SOLUTION

Option 2: Use an INDEX() filter

The following workaround was provided in the Community and found helpful by multiple readers: How to hide a column when showing percent difference.
  1. Select Analysis > Create Calculated Field to create a calculated field.
  2. In the Calculated Field dialog box, type a name and the following formula, and then click OK:
    index()!=1
  3. Drag one copy of the newly created calculated field to Detail on the Marks card and a second copy to the Filters shelf.
  4. In the Filter dialog box, select True, and then click OK.
CLICK TO EXPAND SOLUTION

Option 3: Create a calculation for each column in the view

The attached example workbook uses the sample data set Superstore to demonstrate the following directions:

  1. 1. Create a calculated field with a name like "Consumer Profit" with a calculation similar to the following:
    IF [Segment] = "Consumer"
    THEN [Profit]
    END
    
  2. Repeat step 1 for "Corporate Profit", "Home Office Profit", "Consumer Sales", "Corporate Sales", and "Home Office Sales"
  3. Create a calculated field with a name like "Corporate Sales Difference" with a calculation similar to the following:
    WINDOW_MIN( SUM([Corporate Sales ]))
    -
    WINDOW_MIN( SUM([Consumer Sales]))
  4. Repeat step 3 for "Home Office Sales Difference"
  5. Remove [Sales] and [Profit] from the original view
  6. Drag [Consumer Profit] to Text on the marks card
  7. In the data pane, double-click [Corporate Profit], [Corporate Sales Difference], [Home Office Profit], and [Home Office Sales Difference] to add all of the calculated fields to the view.
  8. Right-click [Corporate Sales Difference] and select Compute Using > Cell
  9. Repeat step 8 for [Home Office Sales Difference].
CLICK TO EXPAND SOLUTION

Option 4: Pivot Measure Values

Pivot Measure Values in Tableau Prep. Note: Calculated fields cannot be pivoted in Tableau Desktop, so the pivoting must be done in Tableau Prep.

  1. Connect to the "Orders" table
  2. Click the plus on Orders, and select Add Step
  3. In Clean 1, create a calculated field named [Dummy Measure] with the formula: 1
  4. Click the plus on Clean 1, and select Add Pivot
  5. In Pivot 1, add [Dummy Measure] and [Sales] to the pivot
  6. Click the plus on Pivot 1, and select Add Output

Create the View in Tableau Desktop

  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 this example, the calculated field is named "New Measure Values"
    2. In the formula field, create a calculation similar to the following: 
      IF MIN([Pivot1 Names]) = 'Sales'
      THEN SUM([Sales])
      ELSEIF MIN([Pivot1 Names]) = 'Dummy Measure'
      THEN ZN(LOOKUP(SUM([Sales]),-1)) - (LOOKUP(SUM([Sales]),-3))
      END
  3. Create a calculated field with a name like "Color Values" with a calculation similar to the following:
    IF MIN([Pivot1 Names]) = 'Dummy Measure'
    THEN [New Measure Values]
    END
  4. Drag [Order Date] and [Pivot1 Names] to the Columns shelf
  5. Drag [Category] and [Sub-Category] to the Rows shelf
  6. Drag [New Measure Values] to Text
  7. Drag [Color Values] to Color on the Marks card
  8. On the Marks card, select Square in the dropdown menu
  9. Click Color and click Edit Colors…
  10. In the Edit Colors dialog, select Orange-Blue-White Diverging from the Palette dropdown, and click OK

Additional Information

By design Tableau Desktop will show a blank column when there is not enough data to compute the Difference. This helps the user understand the direction and scope of the difference.

If the view contains multiple measure, then only options 3 and 4 will provide the desired results. If the view is connected to a cube data source, then options 1 or 2 will work.

Table calculations may be wrong if the Compute using settings are incorrect. For more information, see Transform Values with Table Calculations

To voice your support for the inclusion of this feature in a future release, vote on the Simple Textual Formatting Abilities idea on the Tableau Community Forum.

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