topic Re: Count based on First Date in QlikView

Count based on First Date

jduenyas — Mon, 26 Sep 2011 17:45:36 GMT

(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

Re: Count based on First Date

its_anandrjs — Mon, 26 Sep 2011 18:32:23 GMT

Hi,

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

HTH

Rgds

Anand

Count based on First Date

jduenyas — Mon, 26 Sep 2011 18:46:57 GMT

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

Count based on First Date

jduenyas — Mon, 26 Sep 2011 23:01:42 GMT

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

Re: Count based on First Date

its_anandrjs — Tue, 27 Sep 2011 04:05:02 GMT

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

Re: Count based on First Date

SunilChauhan — Tue, 27 Sep 2011 07:19:51 GMT

see the attached file

in dimension

sales_person

and in expression

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

hope this helps

Re: Count based on First Date

its_anandrjs — Tue, 27 Sep 2011 07:30:05 GMT

Hi,

Might be it is your solution

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

Rgds

Anand

Re: Count based on First Date

Tue, 27 Sep 2011 13:19:41 GMT

Please take a look at the attached QVW.

Re: Count based on First Date

jduenyas — Tue, 27 Sep 2011 15:32:17 GMT

Thanks. It is correct.

Works like a charm.