8 Replies Latest reply on Jul 10, 2019 4:41 PM by John Sarantos

# IF statement Calculated Field

H

I am trying to use an if statement to create a calculated field in tableau that check to see if multiple fields are filled up/have values then "complete" if there is no value in either of the fields then "incomplete".

I also want to create another field that calculate the percent of completion. So for instance if 1 out of 2 fields is null then its 50% complete.

Any help will be much appreciated.

Thank you

• ###### 1. Re: IF statement Calculated Field

One calculated field should do this. Something like:

(IF ISNULL([Field 1]) = True THEN 0 ELSE 1 END +

IF ISNULL([Field 2]) = True THEN 0 ELSE 1 END +

IF ISNULL([Field 3]) = True THEN 0 ELSE 1 END) /

[Total Number of Fields]

If the value is 1 then all have a value. If not then the value is your percentage.

1 of 1 people found this helpful
• ###### 2. Re: IF statement Calculated Field

If I understand correctly, I think this is what you need:

( IF NOT ISNULL([measure 1]) then 1 else 0 END)
+

( IF NOT ISNULL([measure 2]) then 1 else 0 END)

+

( IF NOT ISNULL([measure 3]) then 1 else 0 END)

...

Add up 1s and 0s for as many measures as you need to check.

If the sum of that calc is equal to the number of measures, they none of the measures are NULL.

Based on that, you can then set a COMPLETE or INCOMPLETE value.

I would do it in two calcs, but you could combine all that into one calc if you wanted.

1 of 1 people found this helpful
• ###### 3. Re: IF statement Calculated Field

Thanks for the reply. I like your calculation however it should be "1" or "complete" if there are values in all the fields/measures I am using. so for instance if all the 5 fields I am using have values in them or is not null then complete. if one of the fields is missing a value even if the other fields have values then "incomplete". I have 12 fields that the if statement should reference to see if all fields is complete/have values.

• ###### 4. Re: IF statement Calculated Field

Thanks for the reply. I like your calculation however it should be "complete" if there are values in all the fields/measures I am using. so for instance if all the 5 fields I am using have values in them or is not null then mark as complete. if one of the fields is missing a value or is null even if the other fields have values or is not null then mark as "incomplete". I have 12 fields that the if statement should reference to see if all fields is complete/have values.

• ###### 5. Re: IF statement Calculated Field

Hi Taylor,

BR,

NB

• ###### 6. Re: IF statement Calculated Field

What I proposed is just like Ken's.  You have to look at each measure individually, and if not null then add a 1.  If you have 12 of these, then you'll have 12 chunks of IF-THEN-1-ELSE-0.  And if all of that adds up 12, then all measures are complete.  And if it adds up to 12, then you take it one more step to  say,

IF [check them all calc] = 12 then "Complete" else "Incomplete" END

• ###### 7. Re: IF statement Calculated Field

OK - try this on for size:

1st calc for Complete/Incomplete:

IIF(isnull([Field A])

or isnull([Field B])

or isnull([Field C])

... (Do this for all of your fields

or isnull([Field Z]), 'Incomplete', 'Complete')

2nd calc for % Complete:

SUM(

IIF(isnull([Field A])

or isnull([Field B])

or isnull([Field C])

... (Do this for all of your fields)

or isnull([Field Z]), 0, 1))

/

COUNT(

IIF(isnull([Field A])

or isnull([Field B])

or isnull([Field C])

... (Do this for all of your fields)

or isnull([Field Z]), 0, 1))

Does that get at what you're going for?

1 of 1 people found this helpful
• ###### 8. Re: IF statement Calculated Field

Some dummy data would be great, but my (untested) approach is different ....

If isnull([Field1]+[Field2]+[Field3]+[Field4]+[Field5]+[Field6]+[Field7]+[Field8]+[Field9]+[Field10]+[Field11]+[Field12])

then "incomplete"

else "complete"

end

1 of 1 people found this helpful