8 Replies Latest reply on Sep 21, 2017 1:32 AM by Mahi Jain

# IF NULL THEN ZERO CALCULATION

I have the following calculated field:

IF [Open Seats]>=0 THEN [Open Seats] END

When Open Seats IS NOT greater or equal to zero it's returning NULL, but I need it to return 0. How Can I do that?

I tried the following, although valid, still shows NULL.

IF [Open Seats]>=0 THEN IFNULL([Open Seats],0) END

• ###### 1. Re: IF NULL THEN ZERO CALCULATION

IF [Open Seats]>=0 THEN [Open Seats] else 0 END

1 of 1 people found this helpful
• ###### 2. Re: IF NULL THEN ZERO CALCULATION

Ha! I tried that one before, expect that I had 0 in "" "" and it wasn't working! Thanks again for your help!

1 of 1 people found this helpful
• ###### 3. Re: IF NULL THEN ZERO CALCULATION

You can create a new calculated field : ZN([Open Seats]).

1 of 1 people found this helpful
• ###### 4. Re: IF NULL THEN ZERO CALCULATION

Divya -- I thought of suggesting that.

The logic of the original calc would return 0 for negative numbers too.  I assumed that was part of the expectation.  And ZN won't do that.

But if the data would never have negatives, then ZN is a more direct approach to this question.

1 of 1 people found this helpful
• ###### 5. Re: IF NULL THEN ZERO CALCULATION

Joe-yes, I  agree . ZN() works only if there are no negative values.

• ###### 6. Re: IF NULL THEN ZERO CALCULATION

I have a same type of questions here . in excel ,  my formula is =IF(IFERROR(ROUNDDOWN([ERP/(ActualSpend]/12),0),0)>=13,13,IF(IFERROR(ROUNDDOWN(([ERP/(ActualSpend]/12),0),0)<0,0,IFERROR(ROUNDDOWN([ERP/(ActualSpend]/12),0),0)))

I am trying to write create the same Calculated field in Tableau as following

if ifnull(round([ERP]/[Actual Spend(12M)]/12,0),0)>=13 then 13

else

if ifnull(round([ERP]/[Actual Spend(12M)]/12,0),0)<0 then 0 else IFNULL(ROUND([ERP]/[Actual Spend(12M)]/12,0),0) END

END

Please advise  where goes wrong . Is IFERROR equivalent to IFNULL?

• ###### 7. Re: IF NULL THEN ZERO CALCULATION

Vicky -- You should start a new thread.  Pretty much only the people who participated in this one previously are going to see this.  I'm not familiar with IFERROR in excel, so I can't help with this one.

When you start the new thread, make a note of the behavior you are getting with the syntax you are using.

• ###### 8. Re: IF NULL THEN ZERO CALCULATION

Hi Vicky,

Did you get solution for your question on IFERROR?

ex- =IFERROR(VLOOKUP(F5,'Quote'!\$A\$1:\$AY\$5982,15,FALSE),'Quote'!AZ2)