What is ZN function in Tableau?
ZN function in tableau is the logical function, which is use to returns expression if value is not a NULL else it returns zero value. It only works for Numeric field/value or measures. ZN function in tableau changes the null values to 0 (zero). ZN function works for both level like row level and aggregate numbers.
So suppose you have numeric field or measure as “profit”, then you can use ZN to make all NULL values as zero so that it will be easy and fast to calculate by Tableau to return the values.
This function will give the error if you will use with String data type, Date or Boolean data types.
ZN Syntax in Tableau
ZN(expression)
Expression is the numeric field or measure which you will try to use from your dataset
Lets take 3 different example to understand more for ZN function:
Example #1: Simple ZN Function in Tableau
Let see in below example we are using Zn function for Total Revenue as the total revenue has NULL or blank value for country “Angola”. if you can to replace NULL with zero then you can use ZN function
Applying simple ZN function will helpful if you are showing table view for numbers and few cell are NULL or blank, so the presentation should not be blank it should show zero will be better.
ZN([Total Revenue])
Output will look like as shown below image, red line shows zero appear once use ZN function in tableau
Example #2: Apply for aggregated number
Lets take example to calculate Profit% based on total profit and Total Revenue. then we can use SUM() aggregated function with ZN() function. as show below
ZN(SUM([Total Profit]) / ZN(SUM([Total Revenue]))
Example #3: ZN Function apply with IF statement
lets take if we have parameter to select profit and Profit% then we can use below IF statement in Tableau to show both using drop down options, here we will use the ZN function to make it dynamic and replace NULL with zero to do proper calculation
IF [Filter Selection] = "profit" THEN ZN(SUM([Total Revenue])) - ZN(SUM([Total Cost])) ELSEIF [Filter Selection] = "profit%" THEN ZN(SUM([Total Revenue])) - ZN(SUM([Total Cost])) / ZN(SUM([Total Revenue])) END
Conclusion
ZN function is very useful for numeric calculation to replace NULL/blank value with Zero values. This function helps to present your data and also make your data presentable. And it is useful for all type of aggregated functions.