3 Replies Latest reply on Aug 31, 2016 6:36 AM by Nikita Goldovsky

    How to Select Only One Value Between Two

    Nikita Goldovsky

      Hello-

       

      I have a data set of opportunities with contract values. However, an opportunity may appear twice in my data set if the contacts that are associated with the opportunity have different "Contact Associated Types".

       

      "Contact Associated Type" can take on the value of "Direct" or "Indirect"

       

      Opportunity IDContact Associated TypeAmount
      1234Direct$6000
      1234Indirect$6000

       

      I need some kind of logic that in cases when both Direct and Indirect are present in  "Contact Associated Type", the amount is only associated with the Direct entry.

       

      If only indirect value exists for an opportunity ID, then continue to consider the amount.

       

      Desired output example:

       

      Opportunity IDContact Associated TypeAmount***New Amount
      1234Indirect60000
      1234Direct60006000
      5678Indirect50005000
      9999Direct40004000

       

      Thank you.

        • 1. Re: How to Select Only One Value Between Two
          john.christopher

          (VALUE_A + (IF ISNULL(VALUE_B) THEN 0 ELSE VALUE_B END))

          • 2. Re: How to Select Only One Value Between Two
            chris.moore.11

            What you can do is count the distinct contact types for each ID. If the count is two then you can use logic for if direct then amount else null. If the count is 1 then amount.

             

            See the the calculated fields "contact type count" and "new amount" in the attached.

             

            contact type count:

            {fixed [Opportunity ID]: COUNTD([Contact Associated Type])}

             

            new amount:

            if [contact type count] = 1 then [Amount]

            ELSEIF [Contact Associated Type] = "Direct" then [Amount]

            else null end

            1 of 1 people found this helpful
            • 3. Re: How to Select Only One Value Between Two
              Nikita Goldovsky

              This is the correct answer to my question.

               

              Unfortunately, I realized that an opportunity can also have two contacts of the same contact associated type, in which case I also need to deduplicate the amount of value that is allocated across the contacts.

               

              To do that, I added another calculated field counting just the direct opps.

               

              Count of Direct Opp Types:

              { FIXED [Opportunity ID]: SUM(IF [Contact Associated Type]="Direct" THEN 1 ELSE 0 END)}

               

              And added the following line of text to your "new amount" calculation.

               

              ELSEIF [Count of Direct Opp Types] = 0 then [Amount]/[Contact Type Count]

               

              Thank you for your help.