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

    How to Select Only One Value Between Two

    Nikita Goldovsky



      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


      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


      Thank you.

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


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

            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.