Count customers with aggr

Sat, 27 Jul 2013 23:47:12 GMT

I have the following:

'CustomerData': A table of DATES and CUSTOMERS, each register show the date when a customer made a purchase. Fields Date, Customer.

'Date_Star' : A table with all the dates from CustomerData.Date. Only a field called DateStar

'Date_End' : A table with all the dates from CustomerData.Date. Only a field called DateEnd.

The user can select a 'DateStar' an a 'DateEnd'. Obviously DateStar <= DateEnd.

QlikView must select those registers that the Date is between DateStar and DateEnd, and search if for each customer exist other register at least 30 days. QlikView count how many customers match.

Example:

DateStar 04/02/2013 (Feb 04, 2013)

DateEnd 14/02/2013 (Feb 14, 2013)

Then, this registers are between the dates given:

Date Customer

04/02/2013 2190038

11/02/2013 2190034

14/02/2013 2190031

And checking the data:

Customer 2190038: previous purshase 06/01/2013. Then 04/02/2013 - 06/01/2013 = 29 days. <-- (Less than 30)

Customer 2190034: previous purshase 30/10/2012. Then 11/02/2013 - 30/10/2012 = 104 days. <-- (Greater than 30)

Customer 2190031: previous purshase 14/01/2013. Then 14/02/2013 - 14/01/2013 = 31 days. <-- (Greater than 30)

QlikView response should be: 2.

I'm using Aggr but don't work.

Thanks.

Re: Count customers with aggr

swuehl — Sun, 28 Jul 2013 14:01:34 GMT

Maybe like attached using a script calculated field for the interval and set analysis?

Re: Count customers with aggr

Mon, 29 Jul 2013 02:12:43 GMT

Thanks swuehl, the script works perfect !! I can't understant very well how you calculate 'interval', but all it's ok. Thanks again.

topic Re: Count customers with aggr in QlikView

Count customers with aggr

Re: Count customers with aggr

Re: Count customers with aggr