topic How to count if a customer have come back, help with dates please in QlikView

How to count if a customer have come back, help with dates please

Mon, 03 Jun 2013 12:57:17 GMT

I have a table with cusomer information. It´s basics are customer ID, orderID and dates for each happening in the order, I only use First_Date and Latest_Date in the case. I want to know if a customer have come back within 365 days from the latest "Latest_Date".

If so, the First_Date of a OrderID should be within 365 days from the Latest_Date of a earlier OrderID

Please see attached file.

Re: How to count if a customer have come back, help with dates please

Gysbert_Wassenaar — Mon, 03 Jun 2013 13:54:27 GMT

Try sum(aggr(if(max(Första_Händelse)>max(Sista_Händelse) and max(Första_Händelse)-max(Sista_Händelse) <365 and count(PERSID)>1,1,0),PERSID))

Re: How to count if a customer have come back, help with dates please

Mon, 03 Jun 2013 19:32:07 GMT

Thanks. quit helpful but don´t solve it completly.

Somehow it count people that only have one order so I simply added a if-statement in the beginning.

But the part in the expression that state that the First_date - Latest_Date should be << 365 days don´t work. Also, I found out that some people have placed a new order before the first are closed, in case the customer should not be counted for.

I think I have to look at a alternative solution, maybe do a intervall match in the script or mapp the dates somehow.

(AGGR(COUNT(DISTINCT AEVERKNR1), AKTPNR) >> 1,

sum(aggr(

if(max(Date(Första_Händelse)) >> max(Date(Sista_Händelse)) AND

// max(Date(Första_Händelse)) - max(Date(Sista_Händelse)) >> 0 AND

max(Date(Första_Händelse)) - max(Date(Sista_Händelse)) << 365 and count(AKTPNR) >> 1, 1, 0), AKTPNR)))