3 Replies Latest reply on Jan 14, 2020 3:46 PM by swaroop.gantela

    IF Statement Help!!

    Jun Lian
      Contact ID
      Contact Name
      Product Line
      Carry or Not
      Calculation
      1Super Store112

      2

      Null1
      332
      4Null1
      5Null1
      662
      772
      Contact IDContact Name
      Calculation
      1Super Store2

      Hello All,

       

      I'm trying to write a if statement to add points for contacts.

           - First table shows to data i'm working with (Carry or Not displays the Product Line # if it's been sold from contact).

           - Second table shows the result i'm getting.

           - In my Calculated Field (Below) Super Store is showing to have 2 points even though it didn't meet the requirements of at least Product 1,5, and 6.

       

      I'm not sure where exactly i'm going wrong, but if someone can please show me where I went wrong that would be greatly appreciated!

       

      IF COUNT([Carry or Not]) = 7 THEN 3                                                                                                               //3 points if you sold all Product Lines

          ELSEIF (([product Line] = '1') = ([Carry or Not] = '1')) AND (([product Line] = '5') = ([Carry or Not] = '5'))

                  AND (([product Line] = '6') = ([Carry or Not] = '6')) THEN 2                                                                    //To earn 2 points you have to sell from at least Product Line 1,5, and 6

          ELSEIF COUNT([Carry or Not]) >= 2 THEN 1                                                                                                //1 point if you sold at least 2 Product Lines

          ELSE 0                                                                                                                                                            //0 point if didn't sell any

      END

        • 1. Re: IF Statement Help!!
          swaroop.gantela

          Jun,

           

          I think the issue is the mixing the count across the whole Contact,

          and the checking of individual product lines.

           

          For me, it is easier to see with Level of Detail calculations:

          //3 points if you sold all Product Lines
          IF { FIXED [Contact ID],[Contact Name]:COUNTD([Carry or Not])} = 7 THEN 3

           

          //To earn 2 points you have to sell from at least Product Line 1,5, and 6

          // flag any row of 1/5/6 if carry=1 and then sum them up to see if it's 3


          ELSEIF { FIXED [Contact ID],[Contact Name]:SUM(
              IF ([Product Line]='1' AND  [Carry or Not]='1')
                OR ([Product Line]='5' AND  [Carry or Not]='5')
                OR ([Product Line]='6' AND  [Carry or Not]='6')
              THEN 1 ELSE 0 END)}=3 THEN 2

           

          //1 point if you sold at least 2 Product Lines
          ELSEIF { FIXED [Contact ID],[Contact Name]:COUNTD([Carry or Not])} >= 2 THEN 1

           

          //0 point if didn't sell any
          ELSE 0                                                                                                                                                           

           

          END

           

          please see workbook v2018.3 attached in the Forum Thread:

          IF Statement Help!!

          1 of 1 people found this helpful
          • 2. Re: IF Statement Help!!
            Jun Lian

            Thank you very much!!

             

            I've never thought about summing and counting like that, thank you for teaching me something new

            • 3. Re: IF Statement Help!!
              swaroop.gantela

              Jun,

               

              I'm glad that was helpful.

              All the best.