1 Reply Latest reply on Jun 26, 2018 6:23 AM by Chad Clauson

    Nested Case to Return One Value?

    Chad Clauson

      I'm trying to display a KPI using a calculated field but it shows me multiple shapes based on my data. Essentially, I want to display a green check mark if ALL my records have "Y" in [A]; yellow exclamation point if AT LEAST 1 record shows "N" in [A] and the number 1 in [B]; and a red x if AT LEAST 1 record shows "N" in [A] and a number 2 or higher in [B].

       

      I already assigned the shapes to the possible output. However it displays multiple shapes because it is checking row by row. Here's my current calculated field. How do I adjust it so that it will only return 1 shape? Thanks!

       

      IF ([A] = "Y") THEN "Green"

      ELSEIF [A] = "N" AND [B] = 1 THEN "Yellow"

      ELSEIF [A] = "N" AND [B] >= 2 THEN "Red"

      END

        • 1. Re: Nested Case to Return One Value?
          Jim Dehner

          Hi Chad

          each clause in the IF is taken separately - as soon as one is true then the total condition is satisfied and the next record is processed

          Can't see your data so this will get you close

          IF ([A] = "Y")    and [B}='Y' and [c] ='Y"                           THEN "Green"

          elseIF ([A] = "N")    and [B}='N' and [c] ='N"   Then Red

          else "yellow" end

           

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.