# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Skip the ticket, Chat with Qlik Support instead for instant assistance.
cancel
Showing results for
Did you mean:
Specialist

## Count of contacts based on First Date

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

1 Solution

Accepted Solutions
Not applicable

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

6 Replies
Not applicable

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

Creator III

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.

Specialist
Author

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

Creator III

Much better!

Luminary Alumni

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.

Luminary Alumni