Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Identifying repeat customers

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_IDArrival_Date
112189 1 Jan 2014
1137123 Jan 2014
1046217 Jan 2014
12012111 Jan 2014
11218911 Jan 2014 <-- I want to be able to identify this client and this event
11963412 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

13 Replies
rubenmarin

Hi Lee, when loading the data you can set a flag doing a sorted load:

Data:

NoConcatenate

LOAD Client_ID,

  Arrival_Date,

  If(Peek(Client_ID)=Client_ID and Arrival_Date-Peek(Arrival_Date)<=14, 1, 0) as revisit

Resident Source_Data Order by Client_ID, Arrival_Date;

A Sum(revisit) returns '2' with your sample data

Note that this will count a gap less than 14 days to the previous visit, if there is a visit for Client 112189 on 20-Jan it will count as a revisit of the 12-Jan visit.

If you want to avoid this you can create another field wich carries the day difference from the first visit.

vikasmahajan

Second alternative attached sample. So 112189 is visited 3 times

Vikas

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.
Not applicable
Author

Thanks Ruben - How would I get the days difference? I would like to be able to use a slider to adjust the representation-range value? I've not used the PEEK function before, but from your example I'm assuming there is an easy way to get the number of days since the previous presentation irrespective of the number of days?

Not applicable
Author

Cheers Vikas, I'll have a look when I have access to a Qlik-PC.

rubenmarin

Hi Lee, Peek() is a way to acces the previous loaded file in the table (because of that, the "Order by" settings are important).

You can store the values that meet the criteria:

If(Peek(Client_ID)=Client_ID and Arrival_Date-Peek(Arrival_Date)<=14,

Arrival_Date-Peek(Arrival_Date)) as DaysFromLastVisit,

If(Peek(Client_ID)=Client_ID and Arrival_Date-Peek(Arrival_Date)<=14,

Arrival_Date-Peek(Arrival_Date)+Alt(Peek(DaysFromFirstVisit), 0) as DaysFromFirstVisit,


Note that this is a script solution, if you change '14' to a value stored in a variable (using a slider-->in front-end), you'll need to do a reload to apply the new value to the data load.

Not applicable
Author

Thanks again - that isn't going to work for me, unfortunately. I could do the number of days since the last arrival as a part of the data load (join the table against itself by client_id), but I was hoping that Qlikview would support a way doing this using set analysis. I was hoping that there was something along the lines of:

COUNT({$<CLIENT_ID=P({CLIENT_ID={'[This-Client_id]'}, ARRIVAL_DATE={'>=[This-CLIENT_ID's-Arrival-Date-minus-$(vDaysBetween)-days]}>}CLIENT_ID)>CLIENT_ID)

Not applicable
Author

Hi Vikas,

This is alright for counting all presentations, but it doesn't seem to factor for the within 14 days component, or just for representations.

Thanks again though.

rubenmarin

Hi Lee, set analisys is applied to the whole table, not row by row, so you can't use the Client_ID or the Arrival_Date of each row in set analysis.

I think you'll need some auxiliar field or flag previously calculated in script to use in front-end. If you create a Field that stores 1 when the visit is a revisit you will only need to do a sum of this field to count the revisits.

Not applicable
Author

Thanks Ruben - looks like the only way at the moment would be to add a flag to say that it is re-presentation, and a field to calculate the number of days since the previous presentation. Seems a bit kludgy .

It would be great in the future if Qlik added functionality to allow past-analysis against {<Value={'this.Value'}>}. Implementation of functionality that specifically referenced an object through a this keyword.

Thanks again,

Lee