topic Re: Count of contacts based on First Date in QlikView

Count of contacts based on First Date

jduenyas — Mon, 26 Sep 2011 17:21:12 GMT

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

Re: Count of contacts based on First Date

Mon, 26 Sep 2011 19:54:44 GMT

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

Count of contacts based on First Date

mazacini — Mon, 26 Sep 2011 20:01:51 GMT

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.

Re: Count of contacts based on First Date

jduenyas — Mon, 26 Sep 2011 20:49:02 GMT

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

Count of contacts based on First Date

mazacini — Mon, 26 Sep 2011 22:52:58 GMT

Much better!

Re: Count of contacts based on First Date

Glenn_Renwick — Tue, 07 Oct 2014 20:39:05 GMT

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.

Re: Count of contacts based on First Date

Glenn_Renwick — Wed, 08 Oct 2014 09:53:09 GMT

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))