The Tableau ZN is one of the Number Functions that will return the original expression or values if the cell is not null; Otherwise, it returns Zero. In simple English, the ZN function replaces the NULL values with 0 and prints the Not NULL values as it is.
Remember, this Tableau ZN function only works for NULL values but not for the non-empty cell. It returns the blank or empty cell for No data. So, always use this function to replace NULLS with Zeros.
The Syntax of the Tableau ZN function is as shown below.
ZN([Column_Name or <expression>])
The Column_Name or <expression> should be a Numeric value or Number Column; otherwise, ZN won’t work.
Tableau ZN function Examples
We use the report below to demonstrate the ZN Number or logical function. If you observe the report, there are four sequences for each month, each with the order and sales. However, there are NULLS values for orders and Sales in the fourth sequence of February, June, August, and the 2nd and 3rd sequence of November. We will use this Tableau ZN function to replace these NULLS with Zeros.
First, we must create a calculated Field to write an expression containing the Sales’s ZN function and name it the ZNSales. To do this, right-click the empty space within the Measures shelf to choose the Create Calculated Field.. option.
ZN([Sales])
Similarly, create one more calculated field to deal with the NULLS in orders and name them ZNOrders.
ZN([Orders])
Double-click the ZNSales and ZNOrders calculated fields or drag them to Measure Values to add them to the report. In the Tableau report, all the NULLS are replaced with zeros.
Tableau ZN function Calculations
Replacing NULLS with Zeros is a small part of the Tableau ZN function. The real advantage of using this function comes within the calculations. For instance, this function is handy while calculating averages, standard deviations, etc.
Add Product ID and Years to the Rows Shelf, Orders, and Sales to the Measures Values for this demonstration. Next, change the Aggregate function of both orders and sales from default SUM to AVG. To change the Default Measure Value calculation of the Sales, please click the down arrow beside the SUM(Sales) Change the Measure from Sum to Average. Do the same for the Orders.
Next, add the ZNSales and ZNOrders to the report. If you observe the below Tableau ZN function screenshot, there is a big difference in the average for the marked ones. Let me take the Orders of Product ID 311 to explain the results. In February, there are four sequences, and the sum of the Orders is 1950 + 1240 + 1526 + NULL = 4716.
- The AVG function will not consider NULL as a value. So, AVG = 4716/3 = 1572.
- For ZNOrders columns, the ZN function added Zero to the fourth sequence. It means there are four sequences, hence the AVG = 4716 /4 = 1179.
