6 Replies Latest reply on Dec 8, 2016 5:56 AM by Patricia Escudero Branched to a new discussion.

    IF Statement with multiple options "AND"&"OR"

    Patricia Escudero

      Hi everybody,

       

      I need to create a calculated field which has several conditions in it.

      it is an attribution model, so if three conditions happened at the same time then "True".

      And In the condition there are several options which are possible to get the attribution of Silver, Gold or Platinum.

       

      I have created the calculation below, which is valid, nevertheless it doesn't return the correct value.

      Anybody knows what the issue is?

       

      IF  (ATTR([Sheet1 (Survey Helper File_v1)].[DCE Program])="Web" AND [Current Certification Level]= "Silver")

      AND (ATTR([Sheet1 (Survey Helper File_v1)].[DCE Program])="Offer Data" AND [Current Certification Level]= "Silver" OR[Current Certification Level]= "Gold" OR [Current Certification Level]= "Platinum")

      AND (ATTR([Sheet1 (Survey Helper File_v1)].[DCE Program])="Digital Customer Care" AND [Current Certification Level]= "Silver" OR[Current Certification Level]= "Gold" OR [Current Certification Level]= "Platinum")

       

      THEN "Silver"

      ELSEIF   (ATTR([Sheet1 (Survey Helper File_v1)].[DCE Program])="Web" AND [Current Certification Level]= "Gold")

      AND (ATTR([Sheet1 (Survey Helper File_v1)].[DCE Program])="Offer Data" AND [Current Certification Level]= "Gold" OR [Current Certification Level]= "Platinum")

      AND (ATTR([Sheet1 (Survey Helper File_v1)].[DCE Program])="Digital Customer Care" AND [Current Certification Level]= "Gold" OR [Current Certification Level]= "Platinum")

      THEN "Gold"

       

       

      ELSEIF   (ATTR([Sheet1 (Survey Helper File_v1)].[DCE Program])="Web" AND [Current Certification Level]= "Platinum")

      AND  (ATTR([Sheet1 (Survey Helper File_v1)].[DCE Program])="Offer Data" AND [Current Certification Level]= "Platinum" )

      AND  (ATTR([Sheet1 (Survey Helper File_v1)].[DCE Program])="Digital Customer Care" AND [Current Certification Level]= "Platinum")

      THEN "Platinum"

       

       

      ELSE

      "NO"

      END

        • 1. Re: IF Statement with multiple options "AND"&"OR"
          Matthias Goossens

          Hey Patricia,

           

          Would it be possible to add you packaged workbook?

          This makes it easier to troubleshoot what's going wrong!

           

          Cheers,

          Matthias

          • 2. Re: IF Statement with multiple options "AND"&"OR"
            Patricia Escudero

            hi Matthias, i added the file in a reply to my own post.. sorry, got messed up..

            • 3. Re: IF Statement with multiple options "AND"&"OR"
              Matthias Goossens

              Hey Patricia,

               

              First try to sort your calculation like this:

               

              IF  

              (

                  (

                      ATTR([Sheet1 (Survey Helper File_v1)].[DCE Program])="Web"

                      AND

                      [Current Certification Level]= "Silver"

                  )

                  OR

                  (

                      (ATTR([Sheet1 (Survey Helper File_v1)].[DCE Program])="Offer Data"

                      AND [Current Certification Level]= "Silver")

               

               

                      OR

                      [Current Certification Level]= "Gold"

                      OR [Current Certification Level]= "Platinum"

                  )

                  OR

                  (

                      (ATTR([Sheet1 (Survey Helper File_v1)].[DCE Program])="Digital Customer Care"

                      AND

                     [Current Certification Level]= "Silver")

                      OR

                      [Current Certification Level]= "Gold"

                      OR

                      [Current Certification Level]= "Platinum"

                  )

              )

              THEN "Silver"

              ENd

               

               

               

              This makes is way clearer what you are trying to do.

              The above example is the one for "Silver"

               

              I adjusted some of the AND/OR words and now I get the following result:

               

              I'm quite sure this is not right, since I don't know the logic.

              If you could explain me the logic of silver, I can adjust your calculation.

               

              Cheers,

              Matthias

              • 4. Re: IF Statement with multiple options "AND"&"OR"
                Patricia Escudero

                This is the logic, the lowest level reached by the three programs leads the final attribution.

                For Platinum Status, all three have not be Platium

                For Gold and Silver Status, see the options below.

                Your example worked for some countries, but not for Spain, see below, as Digital Customer Care and offer Data don't have any certification level, it should actually not return silver..

                Thanks so much for your help on this, really appreciate it

                Patricia

                • 5. Re: IF Statement with multiple options "AND"&"OR"
                  Matthias Goossens

                  Hey Patricia,

                   

                  I'm sorry but the logic is still not a 100% clear

                  Could you give a full example for 1 country how the setup is supposed to be?

                   

                  Would love to help but I don't fully understand it yet

                   

                  Cheers,

                  Matthias

                  • 6. Re: IF Statement with multiple options "AND"&"OR"
                    Patricia Escudero

                    Let me try explain the rules:

                    I talk about Program level certification which applies to Web, Digital Customer Care and Offer data and Country Maturity Level, which is calculated base on the certification level of those programs in each country in this case.

                     

                    1-In the case of  Venezuela's data in the file, shows that "Web" has reached Gold Program level, "Digital Customer Care" Silver and "Offer Data" has reached Platinum. In this case the Country maturity level should be Silver.

                     

                    2-In the case of Germany: "Web" has reached Platinum level, whilst "Digital Customer Care" and "Offer Data" both have reached Gold level. In this case the Country maturity level would be Gold

                    3-Spain only has Web with Silver level, the other two programs which count for the country calculation didn't reach any silver, gold nor platinum yet, which means Spain has reached no maturity level yet..

                     

                    In the table I pasted yesterday I am showing all the combinations possible for the programs levels which would lead to a Country Maturity Level of Silver, Gold or Platinum, total of 15 combinations, 10 for Silver, 4 for Gold and One for Platinum