The ZN function when used in a calculation returns the default values if it is not nulls, otherwise it returns zeros. In summary, we can say that we use the ZN function to replace null values with zeros within our computations.
Syntax
ZN (Expression) – this function returns the expression if its not null, otherwise returns zero.
For example
This sample dataset shows the sales for different products in different quarters.
If you were to compute the average sales per product, this is what you’ll get.
(And the reason you’re getting such results - is because NULL values are ignored when computing the average)
But suppose instead, you want to replace the NULL values in your computations with zeros. You can do that using the ZN function as shown below.
Notice this calculation replaces NULLs with zeros as you can see when you view the data.
And now using this calculation to compute the average leaves us with.
(From the resulting view, you can see the difference between the two computations – as highlighted by the star shapes.)
Therefore, next time you need to convert a large list of nulls into zeros, don’t forget the ZN function.
The ZN function used in this case is more targeted and easier to operate than other functions, thus helping you significantly improve the efficiency and quality of data analysis. Different situations require different functions.
To check and remind yourselves of these efficient techniques at any time in daily office work, we can specially prepare some custom stickers with the ZN function and the corresponding functions and formulas used in various situations printed on them. In this way, whenever you face complex analysis tasks, these stickers can become your right-hand assistant in quickly recalling and applying relevant knowledge, making your workflow smoother and smoother.
Thank you for reading.