KNOWLEDGE BASE

Replace NULL or Missing Data With Zeros or Existing Data


Published: 27 Mar 2017
Last Modified Date: 24 Oct 2019

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

CLICK TO EXPAND OPTION 1
Option 1: Use ZN
  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])) 
    • The ZN() function will replace any NULL values with zero
  3. Create a calculated field with a name like " Replace empty cells with last value (opt 1) " with a calculation similar to the following:
    • IFNULL(
          SUM([Amount]),
          PREVIOUS_VALUE(0)
      )
    • IFNULL(..., PREVIOUS_VALUE(0)) will replace any NULL values with the last value of this calculation, which creates a running last value.
    • Depending on how the view is built, it may be necessary to compute PREVIOUS_VALUE() differently. See Transform Values with Table Calculations
  4. Replace [Amount] on Text on the Marks card with either [Replace empty cells with zero (opt 1)] or [Replace empty cells with last value (opt 1)] depending on the desired end result
  5. (Optional) Customize the values in the view by right-clicking the value and formatting it as desired. 
To view the steps showed in the video below, please expand the above section.
Note: the video has no sound.
 
CLICK TO EXPAND OPTION 2
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 or zero (opt 2)"
    2. In the formula field, create a calculation similar to the following:
      ZN( IF [Date] >= [Date Parameter]
      THEN [Amount] 
      END )
      
    • The ZN() function will replace any NULL values with zero. The IF statement takes the place of the [Date] filter.
  6. Create a calculated field with a name like "Filtered Amount or last value (opt 2)" with a calculation similar to the following:
    1. IFNULL(
          SUM(
          IF [Date] >= [Date Parameter]
          THEN [Amount]
          END
          ),
          PREVIOUS_VALUE(0)
      )
    • IFNULL(..., PREVIOUS_VALUE(0)) will replace any NULL values with the last value of this calculation, which creates a running last value.
    • Depending on how the view is built, it may be necessary to compute PREVIOUS_VALUE() differently. See Transform Values with Table Calculations
  7. Replace [Amount] on Text on the Marks card with either [Filtered Amount or zero (opt 2)] or [Filtered Amount or last value (opt 2)] depending on desired end result
  8. Remove [Date] from the Filters shelf
  9. (Optional) Customize the values in the view by right-clicking the value and formatting it as desired. 
To view the steps showed in the video below, please expand the above section.
Note: the video has no sound.
 
CLICK TO EXPAND OPTION 3
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. Create a calculated field with a name like "Replace empty cells with last value(opt 3)" with a calculation similar to the following:
    1. IFNULL(
          LOOKUP(SUM([Amount]),0),
          PREVIOUS_VALUE(0)
      )
    • IFNULL(..., PREVIOUS_VALUE(0)) will replace any NULL values with the last value of this calculation, which creates a running last value.
    • Depending on how the view is built, it may be necessary to compute PREVIOUS_VALUE() differently. See Transform Values with Table Calculations
  5. Replace [Amount] on Text on the Marks card with either [Replace empty cells with zero (opt 3)] or [Replace empty cells with last value (opt 3)] based on desired result
  6. (Optional) Customize the values in the view by right-clicking the value and formatting it as desired. 
To view the steps showed in the video below, please expand the above section.
Note: the video has no sound.
 
CLICK TO EXPAND OPTION 4
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 with zeros"
    2. In the formula field, create a calculation similar to the following:
      ZN( IF [Stage] = [Stage (Stage Master List)] 
      THEN [Amount] 
      END )
    • The join creates duplicate data, therefore to remove the duplicates we only want to show data where the dimension is NULL or the value is missing to the same value in the Master list.
    • The ZN() function will replace any NULL values with zero.
  5. Create a calculated field with a name like "New Amount with last value" with a calculation similar to the following:
    1. IFNULL(
          SUM( IF [Stage] = [Stage (Stage Master List)]
          THEN [Amount]
          END),
          PREVIOUS_VALUE(0)
      )
    • IFNULL(..., PREVIOUS_VALUE(0)) will replace any NULL values with the last value of this calculation, which creates a running last value.
    • Depending on how the view is built, it may be necessary to compute PREVIOUS_VALUE() differently. See Transform Values with Table Calculations
  6. Use either [New Amount with zeros] or [New Amount with last value] based on desired end result
  7. (Optional) Customize the values in the view by right-clicking the value and formatting it as desired. 
To view the steps showed in the video below, please expand the above section.
Note: the video has no sound.
 
CLICK TO EXPAND OPTION 5
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 dummy records with NULL measures, and then follow the instructions in option 1 above.

Additional Information

The attached example workbook uses Superstore sample data to demonstrate the above options. All instructions begin from the "Original" worksheet.

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.

If the crosstab only has one measure, then all NULL values can be replaced in the formatting options. See Format null values

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