6 Replies Latest reply: Oct 8, 2014 5:53 AM by Glenn Renwick RSS

    Count of contacts based on First Date

    Josh Duenyas

      Hi all


      I have a list of Customers and a list of sales people.

      Each customer can be contacted by any sales person.

      However the sales person who contacted the customer first claims the customer as his.

      (Each contact with the customer is recorded with date)

      How can I count the occurrences of FIRST contacts so that each salesperson is credited correctly?


      Cust_ID     Sales_Person      Contact_Date      Contact_Type

      ABC         A                 3/1/11            Email

      BCD         A                 3/12/11           Phone Call

      CDE         B                 3/14/11           Email

      ABC         C                 3/14/11           Phone Call

      DEF         A                 3/14/11           Invitation Sent

      CDE         C                 3/17/11           Phone Call

      CDE         A                 3/20/11           Invitation Sent


      Based on this information Sales Person A is credited with customer ABC (First contact was 3/1/11) and Sales Person B is credited with customer CDE, Sales Person C is NOT credited with contact with ABC etc.


      I wish to view a count of customers for each sales person based on the first contact date (Min(Contact_Date)):


      Sales_Person      Count_Of_Cust_ID

      A                 3

      B                 1

      C                 1




      The formula Count(Cust_ID) is not correct because it counts each presence of Cust_ID