Count of contacts based on First Date

[jduenyas]

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

Etc.

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

Thanks

Josh

[#2 · Accepted solution]

Hi Josh

You can achieve it   with 2 ways :

- in the scrip 

Create a new field for each Customer  which affect the first Sales Person

Load Cust_ID , FirstSortedValue( Sales_Person , Date) as Sales_Affect
Resident Data group by Cust_ID ;

- in the chart

create an expression

=sum( if( Date = Aggr( Min(Date) , Cust_ID) , 1 , 0))

Look at the example in the attachment

JJ

[mazacini]

This is one way you could do it, but probably not a very elegant solution.

You could create a flag on load which would identify whether the contact is a First Contact

e.g.If(EXISTS(CUST_ID),'n','y') as FirstContact

This will set the value of FirstContact to 'y' for all new accounts.

Then with Sales_Person as Dimension, use Set Analysis in your expression  COUNT ({<FirstContact={'y'}>}FirstContact).

This will give you a total per Sales_Person of all FirstContacts = 'y' ie all CUST_ID set up by that Sales_Person.

[jduenyas]

Thank you. That is one way to go about it.

But actually I was looking for the more 'elegant' wasy as suggest by JJ:

=sum( if( Date = Aggr( Min(Date) , Cust_ID) , 1 , 0))

Josh

[mazacini]

Much better!

[Glenn_Renwick]

How would you change the expression to give credit to the saleperson with the last contact date? Max(date) does not seem to work in that expression.

[Glenn_Renwick]

Found the answer

Use NODISTINCT with the Aggr() function because the minimum value may appear twice (two values for the lowest date).

=sum( if( Date = Aggr( NODISTINCT Max(Date) , Cust_ID) , 1 , 0))