Count based on First Date

[jduenyas]

(I published this question in the New to QlikView but no response)

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

[its_anandrjs]

Hi,

I suggest you have to show some thing like this, See the attached sample file.

HTH

Rgds

Anand

[jduenyas]

Thank you Anand but that is incorrect.

Based on my requirement and the sample I provided, Sales Person A has only 3 Customers but your analysis shows 4.

Sales Person A cannot be credited with the customer CDE because Sales Person B was the first to contact this customer on 3/14/11. Although Sales Person A was in touch with CDE he can not claim him as his/her own.

The final analysis should show:

Sales_Person            Count_Customer_ID

A                                3

B                                1

C                                1

Thanks you nonetheless.

Josh

[jduenyas]

Adnan

I am sure you would want to know the correct answer as this is how we learn:

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

You can find the whole discussion in the New to QlikView community where I have posted the same question.

Thank you for pitching in

Josh

[its_anandrjs]

Hi josh,

Thanks i am pretty close to the solution but not getting correct result like

Sales_Person              Count_Customer_ID

A                                        3

B                                        3

C                                        1

But getting 3 for B which is not correct i keep in touch and let you know if i get correct solution.

Rgds

Anand

[SunilChauhan · Accepted solution]

see the attached file

or

in dimension

sales_person

and in expression

=count(if(Contact_Date=aggr(minstring(Contact_Date),Contact_Date), Cust_ID))

hope this helps

[its_anandrjs]

Hi,

Might be it is your solution

http://community.qlik.com/message/152825#152825

Rgds

Anand

[#8]

Please take a look at the attached QVW.

[jduenyas]

Thanks. It is correct.

Works like a charm.