7 Replies Latest reply on Jul 22, 2016 2:19 PM by Derek Wong

# IF THEN if above a threshold

I have following formula...

IF AVG([Actual])>=avg([Goal]) then 'Target Met'

ELSEIF AVG([Actual])<avg([Goal]) then'Target Not Met'

ELSEIF ISNULL(AVG([Goal])) then 'No Goal' END

Now I want to add IF...the Actual is above 40% then 'Target Not Met, but Above Standard'

Where would that fit in the formula above?

• ###### 1. Re: IF THEN if above a threshold

I would place that as the second line.

In tableau, as soon as a condition is met, Tableau stops evaluating.

If you put it AFTER the "Not met" condition, all the 40% stuff would be sucked into that one instead,, and never drop to the extra 40% condition.

• ###### 2. Re: IF THEN if above a threshold

I tried, see below. Not sure why, some of the NULLs are now showing as 'Target Not Met, But'

IF AVG([Actual])>=avg([Goal]) then 'Target Met'

ELSEIF AVG([Actual])>=.4 then 'Target Not Met, But'

ELSEIF AVG([Actual])<avg([Goal]) then 'Target Not Met'

ELSEIF ISNULL(AVG([Goal])) then 'No Goal' END

• ###### 3. Re: IF THEN if above a threshold

It's evaluating from top to bottom, so if the nulls have a value >=.4, then regardless of if they have a null goal, they will drop into the "Target Not Met" category.

To make sure nulls are evaluated first, I'd start with that condition. I then moved the .4 condition to be second, so that that evaluates before the actual>goal and actual < goal conditions. This assumes that an actual value of .4 or greater with a goal <.4 should still be "target not met, but". If that's not the case, you'll have to add one more condition.

IF ISNULL(AVG([Goal])) then 'No Goal'

ELSEIF AVG([Actual])>=.4 then 'Target Not Met, But'

ELSEIF AVG([Actual])>=avg([Goal]) then 'Target Met'

ELSEIF AVG([Actual])<avg([Goal]) then 'Target Not Met'

END

1 of 1 people found this helpful
• ###### 4. Re: IF THEN if above a threshold

how can a null have a value >= .4?  How can a null have a value at all?

I see what you're saying, Derek, and my first reaction was to suggest moving the ISNULL check to the top, but I just couldn't wrap my head around how it would work.

• ###### 5. Re: IF THEN if above a threshold

There's two different fields in play here, Goal and Actual. I was referring to a null of Goal, with a numerical value in Actual.

We have to have the null condition first, because if we have it second, we could have incorrect handling of null values. For example: If goal = null, and actual > .4. if the null condition is second, Tableau will yield "Target not met, but..", which is nonsensical, seeing as there is no target (i.e. the goal is null).

Hope that made my meaning more clear!

1 of 1 people found this helpful
• ###### 6. Re: IF THEN if above a threshold

OMG.  It's Friday.  I don't know what I was thinking.

You're absolutely right.

Now I'm going to back away from the keyboard.

• ###### 7. Re: IF THEN if above a threshold

Haha! Good, I thought I was going crazy . Happy Friday.