6 Replies Latest reply on May 7, 2018 1:24 PM by Silvio Martinez

Populating empty cells with zero

Hello,

How can I populate empty cells with zero. I tried ZN( LOOKUP( SUM([Flag]),0) ), but it doesn't work.

Tableau doesn't go through empty cells at all.

• 1. Re: Populating empty cells with zero

Hi Maxim,

We can change null values to zero. it is very easy in tableau.

we can use the calculated field and change as zero.

use this formula IIF(ISNULL([Actual] ), 0, [Actual] ).

http://kb.tableausoftware.com/articles/knowledgebase/null-values

http://community.tableau.com/docs/DOC-1344

It works fine.

Cheers

sankar

• 2. Re: Populating empty cells with zero

Hi Sankar,

Right, if you want to replace nulls with zero it's not a problem. This is what ZN function does. Actually your formula is equivalent of ZN([Actual], 0).

"No data" and NULL are different cases. No data means that there are no data rows assigned to a cell. While NULL means that you have data rows but don't have some attributes.

• 3. Re: Populating empty cells with zero

BTW, there is an article explaining this problem in the nutshells. Unfortunately a workaround they recommend doesn't work.

Link to the article: Populating Empty Cells with Zeros or Existing Data | Tableau Software

• 4. Re: Populating empty cells with zero

What you're looking for---filling in missing values with 0---is part of Tableau's complex domain completion algorithm.

And, specifically, the crosstab domain completion. From a previous post by Drummey and Mako, who have done all the work documenting this functionality:

crosstab domain completion - this occurs when there are discrete dimensions on opposing Shelves (Rows, Columns, Pages), sparse data (not all combinations of those dimensions exist), and a table calc with the right Compute Using (which is most of them).

In your example, however, you have the same dimension in both the rows and columns shelves, which I suspect prevents domain completion --- Tableau already knows the domain is complete in this case. If you really want this arrangement, you can duplicate the Department dimension and now it works:

Note that Compute Using should be set to Table(Across) in this case, which is the default.

Jim

• 5. Re: Populating empty cells with zero

Hi Jim,

Thank you for the detailed answer.

Maxim

1 of 1 people found this helpful