2 Replies Latest reply on Apr 9, 2018 7:44 AM by matt hins

    subtract prospect if applicant

    matt hins

      Hi, I am counting Prospective Applicants by [Contact ID], which is a unique field in our marketing data. Then I count Applicants separately by [Person ID], which is a unique field from our Admissions Office data. I would like to subtract one Prospective Applicant from the aggregate [Contact ID] count when they are identified as an Applicant with a [Person ID]. Any advice on best way to go about this?

       

      Thanks for the

        • 1. Re: subtract prospect if applicant
          Eric Hammond

          Hi Matt,

           

          If a prospect's [Contact ID] becomes their [Person ID] when they become an applicant, then use the data source tab to do a left join from the Marketing table to the Admissions table on these fields.  Then use a calculated field to count only those prospects who didn't become applicants:

           

          COUNTD (IF [Contact ID] = [Person ID] THEN NULL ELSE [Contact ID] END)

          • 2. Re: subtract prospect if applicant
            matt hins

            Thanks for the feedback Eric,

            I understand where you are going with the calc. There are some limitations and challenges that won't make your exact suggestion work but I came up with a strategy similar to what you've suggested, joining the two data sources and aggregating all [Contact ID] values to return count of Prospects and then to deriving count of Applicants by aggregating [Contact ID] values that have a specific combination of values form three fields in out of the Admissions data source. I'm taking the time now to verify accuracy of the aggregate data coming through the calc.

             

            Thanks for your time,

            Matt