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

    IF THEN if above a threshold

    Diogo Braga

      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
          Joe Oppelt

          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
            Diogo Braga

            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
              Derek Wong

              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
                Joe Oppelt

                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
                  Derek Wong

                  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
                    Joe Oppelt

                    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
                      Derek Wong

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