6 Replies Latest reply on Feb 4, 2019 7:04 AM by Sudhir Rathod

# How to show blank in place of null in a complex IF Statement

Hi Everyone,

I have a scenario where there are 4 different values to a question?  The business wants to summarize them on a scorecard as follows -

Question - Customer expects business to not call them after 8 pm?

Answers could be - Yes, No, n/a or null

In case of yes and no, it's easy to calculate as follows -

ZN(SUM(IF [Do not call] = 'Yes' then 1 else 0 end) /

SUM(IF [Do not call] = 'Yes' then 1

ELSEIF [Do not call] = 'No' then 1 else 0 END))

After placing this measure, n/a can be taken care by simply changing the format to custom by putting this great formula that I found online.  It is #,##0.00%;-#,##0.00;n/a

Up to here it's fine.  But if this question has only null in the answer, our business wants to show the measure as blank space.  I have tried a lot of research and I am sure it's doable as nothing is impossible with Tableau so I am throwing this question out to great Tableau minds to help me out.  Thank you in advance!

• ###### 1. Re: How to show blank in place of null in a complex IF Statement

Hey Sundhir,

I'm not completely sure but have your tried

ELSEIF IsNull([Do Not Call] = TRUE

then " "

I think working with that should lead to an answer. Otherwise you are always welcome to upload the workbook and I can have a look.

Hope it helps. If does please mark it as helpful so other users can refer to it. Thanks!

Best,

Diego.

• ###### 2. Re: How to show blank in place of null in a complex IF Statement

Hi Sudhir,

If you remove the ZN out of your calculation you can force nulls to be created

Which you can then give a special value such as n.p. or n.a. or a blank space if you want using the r/click > Format menu

HTH

Peter

• ###### 3. Re: How to show blank in place of null in a complex IF Statement

Hi,

You may also try this

Use INT(NULL) in ELSE part instead of zero (0) and remove the ZN() from calculation and check. See the below screenshot for one calculated field.

SUM(IF [Do not call] = 'Yes' then 1 else INT(NULL) END) /

SUM(IF [Do not call] = 'Yes' then 1

ELSEIF [Do not call] = 'No' then 1 else INT(NULL) END)

Hope this will help.

Mahfooj

• ###### 4. Re: How to show blank in place of null in a complex IF Statement

Good Morning Mahfooj,

Thanks for giving this a shot.  I tried and it does work but the n/a situation for Medical Privacy gets messed up.  I need to show to n/a if the entire column has nothing but n/a.

Thanks,

Sudhir

• ###### 5. Re: How to show blank in place of null in a complex IF Statement

Hi Peter,

I tried this as well but I need all my scenarios to work as described in the original question.  Somehow if one works then the other doesn't.

Thanks,

Sudhir

• ###### 6. Re: How to show blank in place of null in a complex IF Statement

Hi Diego,

Thanks for giving this a shot.  As described in my original post, I have different scenarios and if one works then the other doesn't.

Thanks,

Sudhir