5 Replies Latest reply on Jul 6, 2015 7:04 AM by Ankur Anwekar

    Setting null values to zero

    Alvin Yeap
    Alvin Yeap May 8, 2012 1:52 PM

      Hi Everyone,

       

      I hope someone can help me.

       

      I'd like to have a table with some measures being aggregated. I've found that if there are no values for a particular cell, that cell will be blank. I'd like to have those cells show a zero instead of being blank. Can anyone tell me if this can be done? I've tried formatting Special Values to zero but this doesn't seem to work.

       

      To show what I mean, I've attached an example worksheet. For example, I'd like the Aaron Bergman/Appliances cell to be '0' instead of blank.

       

      Thanks in advance,

      Alvin.

        • 1. Re: Setting null values to zero
          Alex Kerin
          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
            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.

            • 3. Re: Setting null values to zero
              Alvin Yeap
              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
                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
                  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!