KNOWLEDGE BASE

Replace NULL or missing data with zeros


Published: 27 Mar 2017
Last Modified Date: 25 Jul 2018

Question

How to replace NULL or missing values with zeros.

For example, every project should have three stages in the view, and if there is no [Amount] data for a stage, then the view should show zero.

Environment

Tableau Desktop

Answer

The attached example workbook uses sample data to demonstrate the following options. All instructions begin from the "Original" worksheet.
CLICK TO EXPAND STEPS
Option 1 - Use ZN
This option will only replace NULL data, aka when there is already a blank cell in the crosstab view.
  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 "Replace empty cells with zero (opt 1)"
    2. In the formula field, create a calculation similar to the following:
      ZN(SUM([Amount]))
    3. The ZN() function will replace any NULL values with zero.
  3. Replace [Amount] on Text on the Marks card with [Replace empty cells with zero (opt 1)]
CLICK TO EXPAND STEPS
Option 2 - Replace the filter with a parameter
This option will replace NULL data and data that has been filtered out of the view with zeros.
  1. Click the down arrow next to Dimensions in the data pane and select Create Parameter…
  2. In the Create Parameter dialog, do the following and click OK:
    1. Name the parameter. In this example I will call it "Date Parameter"
    2. For Data Type, select Date
    3. For Allowable values, select All
  3. Right-click [Date Parameter] in the data pane and select Show Parameter Control
  4. Select Analysis > Create Calculated Field
  5. 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 "Filtered Amount"
    2. In the formula field, create a calculation similar to the following:
      ZN( IF [Date] >= [Date Parameter]
      THEN [Amount] 
      END )
      
    3. The ZN() function will replace any NULL values with zero. The IF statement takes the place of the [Date] filter.
  6. Replace [Amount] on Text on the Marks card with [Filtered Amount]
  7. Remove [Date] from the Filters shelf
CLICK TO EXPAND STEPS
Option 3 - Reshape the view and use ZN(LOOKUP())
This option will replace NULL, missing, or filtered out data with zeros.
  1. Move [Stage] from the Rows shelf to the Columns shelf
  2. Select Analysis > Create Calculated Field
  3. 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 "Replace empty cells with zero (opt 3)"
    2. In the formula field, create a calculation similar to the following:
      ZN(LOOKUP(SUM([Amount]),0))
      • The LOOKUP() calculation will look up the value of the current cell (specified by the second argument of LOOKUP() which is a 0) in the view, if that value is NULL then the ZN() function will return zero.
      • Note, for this option to work, there must be a blank cell in the view because this calculation will not add rows/columns, only write into blank cells. For example, if [Stage] is on Rows, then "Hibagon" will only have one stage row. When [Stage] is moved to Columns, then "Hibagon" has three stage columns because at least one other project in the view has each stage.
  4. Replace [Amount] on Text on the Marks card with [Replace empty cells with zero (opt 3)]
CLICK TO EXPAND STEPS
Option 4 - Join the original data to a master list of values
This option will show zeros when the data is NULL, missing or filtered out of the view.
  1. Create a text file that contains a master list of all stages.
  2. Create a cross-database join between the original data and the master list. Use join calculations to join the tables on 1 = 1
    • Join calculations are a new featured added in Tableau Desktop 10.2. For earlier versions, it will be necessary to create dummy linking fields in the underlying data sources.
    • For more information on how to create cross-database joins or join calculations, please see Join Your Data.
  3. Select Analysis > Create Calculated Field
  4. 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 Amount"
    2. In the formula field, create a calculation similar to the following:
      ZN( IF [Stage] = [Stage (Stage Master List)] 
      THEN [Amount] 
      END )
  5. Replace [Amount] on Text on the Marks card with [New Amount]
CLICK TO EXPAND STEPS
Option 5 - Add the missing data to the underlying data set
Edit the original data source to add in the missing data.

Depending on the use case, the best solution may be to add in a dummy records with NULL measures, and then follow the instructions in option 1 above.
 

Additional Information

There are three main scenarios that may create blanks that would need to be replaced with zeros:
  • NULL data: this occurs when the underlying data set contains records but the measure value(s) is NULL or blank. In the attached Excel workbook, "NULL and Missing Examples.xlsx", the project "Brosnya" is NULL for "In-Progress" and "Completed".
  • Missing data: this occurs when there are no records in the underlying data set. In the example data, the project "Hibagon" is missing "In-Progress" and "Completed"
  • Filter data: In the attached workbook, there is a [Date] filter that also filters out "Started" and "In-Progress" for the project "Dalmatian". The project "Dalmatian" has records in the underlying data source for every stage.
All options will replace NULL data with zeros. Options 3, 4, and 5 will replace missing data with zeros. Options 2, 3, and 4 will replace filtered out data with zeros.

It may be possible to show additional rows/columns in the view with the Analysis > Table Layout > Show Empty Rows/Columns option; However, no data can be written into these rows.
Did this article resolve the issue?