When working with data in Tableau, one of the most common problems developers and analysts face is handling missing or empty values. These missing values, known as NULL values, can cause serious issues in calculations and visualizations.
For example, your charts may show gaps, your averages may become incorrect, and your reports may look incomplete.
To solve this problem, Tableau provides a very useful function called ZN(). This function converts NULL values to 0 so your calculations and dashboards work correctly.
Tableau provides this function specifically for handling numeric data, making it easier to clean and prepare data for analysis.
In Tableau datasets, many fields contain NULL values. These nulls can cause issues in Tableau calculations; for example, the average calculation may return incorrect values, or line charts may show inconsistent lines where data is missing.
To handle such data sets with null values, Tableau has the ZN() function, which replaces any null value with zero (0).
In this Tableau tutorial, I will explain the use case for the ZN function. We will also review its syntax, then discuss a few examples using sales and profit data.
What is the Tableau ZN() Function?
In Tableau, the ZN() function is a Number function that replaces null (empty) values with zero while leaving other numeric values unchanged.
It is used in calculated fields to clean data and ensure calculation accuracy, for example, averages by converting missing numeric values to zeros, which are then included in the calculation.
The ZN() function requires a numeric expression as its argument and will return an error if used with non-numeric data types.
This function is mainly used in numeric fields such as Sales, Profit, or Discount, to avoid calculation errors or breaks in visualizations.
Syntax of the ZN() function.
ZN(<expression>) - If the expression is not null, ZN() returns the original value.
- If the expression is null, ZN() returns 0.
Tableau ZN() Function – Examples
In the examples below, we will discuss the use cases of the ZN() function in Tableau.
For the data visualization, I have used the dataset below, which contains blank or null values. Then, using the ZN() function, null values will be replaced with 0 in the visualization and calculations.
Now connect the dataset to Tableau and follow the examples below to see the ZN() function in action.
Example 1: Tableau Replace Null Values Using ZN() Function
In this example, we will replace the Null values of the Sales field with 0 using the ZN() function.
- First, we will create a calculated field using the ZN() function that returns 0 for NULL sales values.
ZN([Sales])- To visualize the data, add the Sales and ZN() function calculation to the Text card and Product to the Rows.
Now, we can see in the ZN() function calculation that the null sales values are replaced with 0.
- To illustrate the impact of NULL and zero values on the calculation, we will show the average for both columns. For this, click on the Analysis tab and select Totals -> Show Columns Grand Totals.
- To show the Average of totals, click on the calculated field and select Measure -> Average. Then, show the average calculation for the Sales field in the same way.
Now, we can see the difference between the average calculation for the column using the ZN() function and the actual Sales values.
Using the ZN() function, NULL values are replaced with 0, and the average is then calculated, including the 0 values.
This way, we can use the ZN() function in Tableau to replace NULLs with 0 and obtain accurate values for table calculations.
Example 2: Use ZN() Function in a Calculation
In this example, we will use the ZN() function to calculate the profit average of the product sales.
- Using the above dataset, create a calculated field that sets Profit and Sales nulls to 0.
// For profit Nulls
ZN([Profit])
//For sales Nulls
ZN([Sales])- Create a calculated field “Profit Margin Zn” that will show profit margin and show 0 instead of NULL values.
// Using above ZN calculations
ZN([Sales])/ZN([Profit])
// Using separate Calculation
ZN([Sales])/ [Profit]- To visualize the data, add the Product to the Rows. Then add Profit, Sales, and Profit Margin Zn to the Text card.
Now, we can see that this replaces null profit values with zero, giving a complete profit margin column.
Example 3: Handle NULL Profit Values Using ZN() Function
In this dataset, we have several null or blank values in the Profit field. To avoid showing these nulls in the chart and table calculations, we will create a calculation using the ZN() function.
- Create a calculated field Profit ZN using the formula below.
ZN([Profit])- Now, add the Profit and Profit ZN function to the Columns and Product to the Rows. With this, we can see that the Null values are replaced with 0 in profit values.
- After this, we will perform calculations on both columns to compare the results.
Click on both fields’ dropdown in the Columns and select Measure -> Average.
- Now, we can see the differences in the average calculation for profit with nulls and profit (ZN) with zero values.
This way, we can get the accurate calculations using the ZN() function when there are Null values in the dataset.
ZN vs IFNULL Function in Tableau
Many beginners get confused between ZN and IFNULL.
| Feature | ZN() | IFNULL() |
|---|---|---|
| Replace NULL with | 0 only | Any value |
| Use case | Numeric fields | Flexible |
| Syntax | Simple | Custom |
Frequently Asked Questions
Q1: What does ZN mean in Tableau?
It means Zero Null (replace NULL with 0)
Q2: Can ZN be used for text fields?
No, only numeric fields
Q3: Is ZN better than IFNULL?
Depends on use case
Q4: Does ZN affect performance?
No major impact
Conclusion
The Tableau ZN function is a simple yet powerful tool for handling NULL values in data. By converting NULL values into zero, it ensures that your calculations, reports, and dashboards work correctly without errors.
However, it is important to use ZN carefully because NULL does not always mean zero. When used correctly, this function can significantly improve the quality of your data analysis and visualization.
Removing NULL values and replacing them with 0 is helpful for calculating accurate values, such as the average of Sales or Profit.
You may also like to read:
I am Bijay Kumar, the founder of SalesforceFAQs.com. Having over 10 years of experience working in salesforce technologies for clients across the world (Canada, Australia, United States, United Kingdom, New Zealand, etc.). I am a certified salesforce administrator and expert with experience in developing salesforce applications and projects. My goal is to make it easy for people to learn and use salesforce technologies by providing simple and easy-to-understand solutions. Check out the complete profile on About us.
