KNOWLEDGE BASE

Hiding Only the Blank Column when Using Difference Table Calculation


Published: 04 Feb 2013
Last Modified Date: 18 Sep 2017

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

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.

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.

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. 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]

Option 4: Use a "scaffolding" field

1. Remove [Sales] and [Profit] from the view

2. Drag the "scaffolding" field to Detail on the marks card

In this example, I am using [Region] as my scaffolding field. See the worksheet "testing the scaffolding field" for more explanation of the requirements of the "scaffolding" field.

3. Create a calculated field with a name like "New Measure Headers" with a calculation similar to the following:

IF MIN( [Region] ) = "Central"

THEN "Sales Difference"

ELSEIF MIN( [Region] ) = "East"

THEN "Profit"

END

4. Drag [New Measure Headers] to the Columns shelf

5. Create a calculated field with a name like "Hide NULL Headers" with a calculation similar to the following:

NOT ISNULL( LOOKUP( [New Measure Headers], 0 ) )

6. Drag [Hide NULL Headers] to the Filters shelf

7. In the Filter dialog, check True and click OK

8. Create a calculated field with a name like "Sales excluding Region" with a calculation similar to the following:

{ EXCLUDE [Region] : SUM( [Sales] ) }

Since we have added [Region] to the view, all measures added to the view will be partitioned by [Region]. Therefore we can use the EXLUDE expression to return the SUM( [Sales] ) as if [Region] was not in the view.

9. Repeat step 8 for [Profit]

10. Create a calculated field with a name like "New Measure Values" with a calculation similar to the following:

IF MIN([Region]) = "Central"

THEN ZN( SUM( [Sales excluding Region] )) - LOOKUP( ZN( SUM( [Sales excluding Region] )), -1)

ELSEIF MIN([Region]) = "East"

THEN SUM( [Profit excluding Region] )

END

11. Drag [New Measure Values] to Text on the marks card

12. Right-click [New Measure Values] on the marks card, and select Compute Using > Table (across)

13. Create a calculated field with a name like "index" with a calculation similar to the following:

INDEX()

14. Double-click [index] in the data pane to add the field to the view

This will allow us to see how INDEX() is being computed in the view so that we know what INDEX() value to exclude.

15. Right-click [index] on the marks card and select Edit Table Calculation…

16. In the Table Calculation dialog, do the following and then close the dialog:

  • Select Specific dimension
  • Check only Segment and Region

17. We see that the blank column has a INDEX() value of 1. Remove [index] from the view

18. Create a calculated field with a name like "Index Filter" with a calculation similar to the following:

INDEX() != 1

19. Drag [Index Filter] to the Filters shelf and click OK to close the Filter dialog

20. Right-click [Index Filter] on the Filter shelf and select Edit Table Calculation…

21. In the Table Calculation dialog, do the following and then close the dialog:

  • Select Specific dimension
  • Check only Segment and Region

22. Right-click [Index Filter] on the Filters shelf and select Edit Filter…

23. In the Filter dialog, check only True and click OK

Option 5: Use Table Calculations

1. Move [Segment] from the Columns shelf to Detail on the marks card

2. Create a calculated field with a name like "Consumer Profit" with a calculation similar to the following:

IF INDEX() = 1

THEN SUM([Profit])

END

3. Double-click [Consumer Profit] in the data pane to add the field to the view

4. Right-click [Consumer Profit] on the Measure Values card, and select Compute Using > Segment

5. Repeat steps 2-4 for "Corporate Profit" and "Home Office Profit"

6. Repeat step 2 for "Consumer Sales", "Corporate Sales", and "Home Office Sales"

7. Create a calculated field with a name like "Corporate Sales Difference" with a calculation similar to the following:

IF FIRST() = 0

THEN

WINDOW_MIN( [Corporate Sales])

-

WINDOW_MIN( [Consumer Sales])

END

8. Double-click [Corporate Sales Difference] in the data pane to add the field to the view

9. Right-click [Corporate Sales Difference] on the Measure Values card and select Compute Using > Segment

10. Repeat steps 7-9 for "Home Office Sales Difference"

11. Remove [Sales] and [Profit] from the original view

Additional Information

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

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.
Did this article resolve the issue?