topic Re: Identifying repeat customers in QlikView

Identifying repeat customers

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Identifying repeat customers

rubenmarin — Fri, 08 Jul 2016 08:11:10 GMT

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.

Re: Identifying repeat customers

vikasmahajan — Fri, 08 Jul 2016 09:29:06 GMT

Second alternative attached sample. So 112189 is visited 3 times

Vikas

Re: Identifying repeat customers

Fri, 08 Jul 2016 10:41:43 GMT

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?

Re: Identifying repeat customers

Fri, 08 Jul 2016 10:42:55 GMT

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

Re: Identifying repeat customers

rubenmarin — Fri, 08 Jul 2016 11:14:09 GMT

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.

Re: Identifying repeat customers

Fri, 08 Jul 2016 12:05:47 GMT

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)

Re: Identifying repeat customers

Sun, 10 Jul 2016 22:15:32 GMT

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.

Re: Identifying repeat customers

rubenmarin — Mon, 11 Jul 2016 07:30:31 GMT

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.

Re: Identifying repeat customers

Mon, 11 Jul 2016 21:44:29 GMT

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

Re: Identifying repeat customers

qliksus — Tue, 12 Jul 2016 01:25:55 GMT

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 ;

Re: Identifying repeat customers

Tue, 12 Jul 2016 04:27:26 GMT

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.

Re: Identifying repeat customers

rubenmarin — Tue, 12 Jul 2016 15:59:32 GMT

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..

Re: Identifying repeat customers

Sat, 16 Jul 2016 18:33:41 GMT

Thanks Guys!

This helped me very much.