topic Repetitive customer in QlikView

Repetitive customer

Tue, 27 Dec 2011 22:15:12 GMT

Hi,

Selecting a date, I need to know for a year back the average of the amount paid by my customers in a purchase and the average of the difference in days between these purchases.

ex:

customer_id= 1 ; 2 purchases (day 10 amount= 20, day 8 amount= 30)

customer_id= 2 ; 3 purchases (day 11 amount= 30, day 7 amount= 40, day 1 amount 50)

customer_id1: avg(amount)= 25 avg(difference) = 2

customer_id2: avg(amount)= 40 avg(difference) = 5

I need the to show only the total:

avg(amount_paid/difference)= ((25+40)/2) / ((2+5)/2)= 32,5 / 3,5= 9,28

Is that possible???

Thanks!

David

Repetitive customer

pover — Tue, 27 Dec 2011 22:53:41 GMT

David,

It's possible, but you'll have to create a new field in your script. You'll first need to order the fact table by date and then customer. Then you can load it using inter-row functions like previous() or peek() to keep track of how many days passed since the last purchase. The first row of each customer would be have an 'empty' difference and then for each future purchase you would subtract the previous row's purchase date from the current row's purchase date. That will give you the average days between purchases and the rest of the formula should be easier.

There might be a fancier way to do it, but that should get the job done.

Karl

Repetitive customer

qliksus — Wed, 28 Dec 2011 03:24:06 GMT

Hi,

Clarify on how you are calculating the Difference

For Custid1 = 10 - 8 = 2

For Custid2 = 11 - (7-1) = 5

So how will you calculate the difference when there is a customer who made purchase on 4 days

Repetitive customer

pover — Wed, 28 Dec 2011 18:07:54 GMT

I think the formula is:

For Custid1 = (10 - 😎 / 1 = 2

For Custid2 = ( (7-1) + (11-7) ) / 2 = 5

Karl

Repetitive customer

rohit214 — Thu, 29 Dec 2011 06:03:29 GMT

hi david

rest of all thing is ok for me

but i am not able to understand to calculate diffrence formula

and suppose puchase is more than 3 then how will you calculate the diffrence

please let me know

thanks

rohit

Repetitive customer

Thu, 29 Dec 2011 07:28:14 GMT

is the average, as karl said:

For Custid2 = ( (7-1) + (11-7) ) / 2 = 5

if there were another purchase from custid2 the difference fórmula would be,

custid2 = ( (7-1) + (11-7) + ( 13-11)) / 3 = 4

But it's OK, I 'll follow karl's advice,

Thanks