KNOWLEDGE BASE

Populating Empty Cells with Zeroes or Existing Data


Product(s): Tableau Desktop
Version(s): All
Last Modified Date: 16 Aug 2016

In a text table you create in Tableau Desktop, you can replace the empty cells with zeroes, even when there are no values for those cells in the underlying data. You can populate empty cells with a custom calculation that uses the LOOKUP() and ZN() functions. And, you can convert the zeroes to related values that exist in the rows or columns of the table using the WINDOW_SUM() function.  
 

Set up the calculation

Before you can populate the empty cells in your text table with zeroes or related values, you create a custom calculation using a combination of the LOOKUP() and ZN() functions. This calculation looks at all the cells displayed in your view. If the LOOKUP() function finds no data in a cell, it returns a null. Then the ZN() function turns the null into a zero.

Step 1 

Open Tableau Desktop and connect to your data source. This example uses the Sample – Superstore data source.

Step 2 

Select Analysis > Create Calculated Field.

Step 3 

In the Calculated Field dialog box, do the following:
  1. Name the field ZN Lookup.
  2. In the formula area, enter: ZN(LOOKUP(SUM([Sales]),0))

Populate empty cells with zeros

Step 1 

From the Dimensions area of the Data pane, drag Category to Columns.

Step 2 

In the Dimensions area of the Data pane, right-click (Control-click on a Mac) Category and chooseDuplicate.

Step 3 

From the Dimensions area of the Data pane, drag Category (copy) to Rows.

Step 4 

From the Measures area of the Data pane, drag ZN Lookup to Text. Empty cells are now populated with zeroes.

Populate empty cells with existing column data

You can populate empty cells with existing column data. To do this, you create two calculated fields that use the ZN Lookup calculation to convert the zeroes to the relevant column values.

Step 1 

Create a new worksheet and name it Vertical.

Step 2 

Select Analysis > Create Calculated Field.

Step 3 

In the Calculated Field dialog box, do the following:
  1. Name the new calculated field Vertical ZN.
  2. In the formula area, type: WINDOW_SUM([ZN Lookup])

Step 4 

From the Dimensions area of the Data pane, drag Category to Columns.

Step 5 

From the Dimensions area of the Data pane, drag Category (copy) to Rows.

Step 6 

From the Measures area of the Data pane, drag Vertical ZN to Text.

Step 7 

Click Vertical ZN on the Text shelf and select Compute using > Table (Down).

The values in the text table display the combined values for each row in the column.


 

Populate empty cells with existing row data

You can populate empty cells with existing row data. To do this, you create two calculated fields that use the ZN Lookup calculation to convert the zeroes to the relevant row values.

Step 1 

Create another new worksheet and name it Horizontal.

Step 2 

Select Analysis > Create Calculated Field.

Step 3 

In the Calculated Field dialog box, do the following:
  1. In the Name text box, type Horizontal ZN.
  2. In the Formula text box, type: WINDOW_SUM([ZN Lookup])

Step 4 

From the Dimensions area of the Data pane, drag Category to Columns.

Step 5 

From the Dimensions area of the Data pane, drag Category (copy) to Rows.

Step 6 

From the Measures area of the Data pane, drag Horizontal ZN to Text.

Step 7 

Click the Horizontal ZN field on the Text shelf and select Compute using > Table (Across). The values in the text table display the combined values of each column in the row.

Show the difference between column and row values

Step 1 

Create a new worksheet and name it Difference.

Step 2 

From the Dimensions area of the Data pane, drag Category to Columns.

Step 3 

From the Dimensions area of the Data pane, drag Category (copy) to Rows.

Step 4 

Select Analysis > Create Calculated Field.

Step 5 

In the Calculated Field dialog box, do the following:
  1. Name the new calculated field Difference.
  2. In the formula area type: [Vertical ZN]-[Horizontal ZN]

Step 6 

From the Measures area of the Data pane, drag Vertical ZN and Horizontal ZN to Detail.

Step 7 

Click Vertical ZN to verify that it is set to Compute Using > Table (Down). Click Horizontal ZN to verify that it is set to Compute Using > Table (Across).

Step 8 

Drag Difference from the Measures area of the Data pane to Text. The Compute Using settings for Difference default to the ones specified on the Detail shelf. The result is a matrix that shows the difference between the column and row values. 

 

Did this article resolve the issue?