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.
Last Modified Date: 09 Feb 2024
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 DesktopAnswer
CLICK TO EXPAND OPTION 1
Option 1: Use ZN
Note: the video has no sound.
CLICK TO EXPAND OPTION 2
Option 2: Replace the filter with a parameter
Note: the video has no sound.
CLICK TO EXPAND OPTION 3
Option 3: Reshape the view and use ZN(LOOKUP())
Note: the video has no sound.
CLICK TO EXPAND OPTION 4
Option 4: Join the original data to a master list of values
Note: the video has no sound.
CLICK TO EXPAND OPTION 5
Option 5: Add the missing data to the underlying data set
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.
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
Vote on the Tableau Community Idea - PREP Clean Step - Menu Option to replace Null values
Thank you for providing your feedback on the effectiveness of the article.
Open new Case
Continue Searching
Knowledge Base
Community
Product Help
Training and Tutorials