6 Replies Latest reply on Feb 27, 2013 5:37 PM by Tracy Rodgers

# Convert Null/Blank values to 0s

I have been trying to figure out how to change ‘NULL’ values to ‘0’s. I tried using “IF ISNULL”, “IIF”, "ZN" and a couple of others but all in vain. For some reason, it’s not converting the NULLs to 0s.

I would appreciate if someone could let me know how to find a solution to this. Thanks!

• ###### 1. Re: Convert Null/Blank values to 0s

ZN() should do the trick if these are really NULL values. Maybe they are Zero Length Strings that just look NULL?

In that case, you might want to use Len() against the field and return 0 when Len([FIeldName]) = 0 ...or you could even combine a ZN() and LEN() test in an if..elseif..else...end statement.

Good luck!

1 of 1 people found this helpful
• ###### 2. Re: Convert Null/Blank values to 0s

Hi Russell,

I have the same problem than Karthik.

I've tried the ZN Function but with no success.

I'm looking at creating "0" value instead no value to be able to run difference calculation.

Based on the table below as an example, I would like to be able to get the following result:

Difference between 2013-08 and 2013-07 in terms of CAR is 30

 Period CAR BUS 2013-05 50 60 2013-06 60 30 2013-07 50 2013-08 30 60 2013-09 50 30 2013-10 60 50 2013-11 60 2013-12 30 30 2013-13 50 50

I have no issue to calculate a difference in Tableau but when it's relative to an empty value, nothing happen.

Regards,

Thomas

• ###### 3. Re: Convert Null/Blank values to 0s

Hi,

I've attached a WorkBook to better illustrate my issue.

Thomas

• ###### 4. Re: Convert Null/Blank values to 0s

Hi Thomas,

I'm unable to open the workbook (it needs to be saved as a twbx file). However, using a calculation similar to the following should be able to help:

zn(lookup(sum([Measure]), 0))

Hope this helps!

-Tracy

• ###### 5. Re: Convert Null/Blank values to 0s

Hi Tracy,

Thanks a lot for your help, it works, your formula did generate 0 values.

But the Measure format generated doesn't allow me to do a Quick Table Calculation.

I'd like to do the following task sequences from thye Measure: Difference => Compute using "Period" => Relative to "2013-08"

I've joined a twbx file.

Best regards,

Thomas

• ###### 6. Re: Convert Null/Blank values to 0s

Hi Thomas,

One way to do this is to first add the desired table calculation to Value. Then, right click on it and select Edit Table Calculation...>click on the Customize... button and save the calculated field.

Then, use this calculated field in the calculation that I previously provided, similar to:

zn(lookup([Calculation1],0))

When you put it on the view, make sure to change the compute using to how you'd like it.

Hope this helps!

-Tracy