Identifying repeat customers

[#1]

Hi all,

Relatively new Qlikview user here with a question (naturally!).

Say I have a table of clients and their date of arrival at a clinic. Each client has a unique ID. How do I isolate those clients that come to a clinic within, say, 14 days of their last arrival date?

Client_ID	Arrival_Date
112189	1 Jan 2014
113712	3 Jan 2014
104621	7 Jan 2014
120121	11 Jan 2014
112189	11 Jan 2014 <-- I want to be able to identify this client and this event
119634	12 Jan 2014
112189	12 Jan 2014 <-- I want to be able to identify this client and this event, but only for a single event (the 11th Jan visit) even though they are still within 14 days of the 1 Jan visit

So client ID 112189 has come back within 14 days. How can I:

     - Identify both the original visit, and the first occurrence of any visit within the 14 days

     - Get a count of all clients within a timeframe that revisit in 14 days? (In the example provided, I would want a count of 2 because it should only count against the visit immediately prior

Any assistance or advice gratefully accepted.

Thank you,

Lee

[qliksus]

May be something like this

count({$<Client_ID=P({1<Num={">0<14"}>}Client_ID)>} Client_ID )

Where Num is calculated in the script which is the difference between the Dates visited

Client:
LOAD * INLINE [
Client_ID ,Arrival_Date
112189, 01/01/2014 
113712, 01/03/2014
104621, 01/07/2014
120121, 01/11/2014
112189, 01/11/2014
119634, 01/12/2014
112189, 01/12/2014
];

load
Client_ID,Arrival_Date,

if( Peek(Client_ID)=Client_ID , (Arrival_Date-Peek(Arrival_Date))+ Peek(Num) ,0) as Num

Resident Client
Order by 1,2;

DROP Table Client ;

[#3]

Thanks Qliksus - Yes, this is pretty much how I am going to have to handle it. I was just wondering if/hoping that the functionality lived within set analysis as that would potentially provide a lot more functionality and versatility.

[rubenmarin]

Hi Lee, I want only remark that this is the only way at the moment I know, but I'm always open to learn new techniques I still unknown ... maybe there is a workaround to do this in front-end, but I see it easier using auxiliary fields; this fields can be to set a flag, the ID of the most recent visit or from the visits in the last 14 days, days from last visit, etc..

[#5]

Thanks Guys!

This helped me very much.