Repetitive customer

[#1]

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

[pover · Accepted solution]

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

[qliksus]

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

[pover]

I think the formula is:

For Custid1 = (10 - 😎 / 1 = 2

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

Karl

[rohit214]

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

[#6]

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