-
1. Re: Setting null values to zero
Alex Kerin May 8, 2012 2:21 PM (in response to Alvin Yeap)Would the calculation:
zn([your measure]) work
zn stands for zeronull (i.e. make all nulls equal to zero)
-
2. Re: Setting null values to zero
Richard Leeke May 8, 2012 2:36 PM (in response to Alvin Yeap)Yes, you can do that in various ways. I've attached an example of the easiest way I can think of - though it can be a little hard to understand what is going on.
There are three sheets in the attached example. The first is your starting point.
Sheet2 shows that even using the ZN() function (which replaces NULL values with zeroes) doesn't help on it's own, because the issue you are dealing with is not underlying rows that have a NULL in them, it's the case where the underlying row doesn't exist at all.
Sheet3 shows one way of forcing those missing rows to get created automagically by Tableau. In many (but not all) cases where you have missing permutations of dimensions, using table calculation functions will cause Tableau to fill in dummy rows. So I've just used a call of the LOOKUP() function to make that happen.
Search the forum for "padding the domain" to see more discussion of this. There has been some good (but deep!) discussion of it in the past couple of days in these two threads: this one started by Alex and this one by me.
-
Zero Nulls Eg_RL.twbx.zip 326.2 KB
-
-
3. Re: Setting null values to zero
Alvin Yeap May 8, 2012 2:59 PM (in response to Richard Leeke)Wow!
Thanks for that, Richard. The ZN(LOOKUP()) has done the trick. I wouldn't have even guessed to use that calculation.
Can I clarify if my understanding of how this works is correct? First we tell Tableau to calculate the SUM of Number of Records at that aggregation level. Then for a particular cell, we use LOOKUP to grab the summed value (which may be null). Lastly, with ZN, we replace nulls with zeros.
Thanks again,
Alvin. -
4. Re: Setting null values to zero
Richard Leeke May 8, 2012 3:32 PM (in response to Alvin Yeap)The LOOKUP() function is intended to let you look up the values in preceding or following rows - so you can calculate trends or whatever. Missing values can make that tricky, so when Tableau sees the LOOKUP() function it thinks "I'd better fill in the gaps so I get a consistent result from looking up at an offset from the current row".
The trick I'm using here is to say "look up the row that is offset by zero rows from the current row" - ie just get the current value. But that just tricks Tableau into generating empty (null) rows for the missing ones.
Finally the call of ZN() replaces nulls with zeroes. That only works when there is an actual null row rather than a missing row, which is why sheet2 (which was what Alex suggested) doesn't work on its own.
-
5. Re: Setting null values to zero
Ankur Anwekar Jul 6, 2015 7:04 AM (in response to Alvin Yeap)Richard - In a case where I filter out only one category, lets say "bookcases" then I loose all the customer with 0 values. How do I still retain those customers with zeros when I filter out by only one category?
Thanks!