topic Re: Frequent Customers in QlikView

Frequent Customers

Tue, 19 Aug 2014 18:27:26 GMT

I am having trouble figuring out how to create a field that gives me the amount of days since the last time the customer checked out. For instance, I have James who checked in on 1/8/2014 and checked out on 1/9/2014. He then came back on 1/12/2014. I want to calculate the amount of days since he last came in. It should say its been 3 days since he last came in. I am thinking of using peek commands and previous commands but I can't figure out a way to do this.

Re: Frequent Customers

JonnyPoole — Tue, 19 Aug 2014 18:36:45 GMT

If you create a straight table with Client as the dimension, and the following expression, it should do a count of the days from the most recent check out and most recent check in

max( total <Client> [Check Out]) - max( total <Client> [Check In])

Re: Frequent Customers

jaimeaguilar — Tue, 19 Aug 2014 18:42:34 GMT

Hi,

if you just want to count the days since last check out taking as reference today, then you can use an expression like this:

Today() - max([Check Out])

if you want your expression to be more dynamic, you would need to use something like this:

max(Date) - max([Check Out])

hope this helps,

regards

Re: Frequent Customers

jaimeaguilar — Tue, 19 Aug 2014 18:44:20 GMT

Also, I forgot that you may need to use Total (for disregarding/ignoring dimension values) or ignoring selections with set analysis

Re: Frequent Customers

GabrielAraya — Tue, 19 Aug 2014 18:49:28 GMT

Hi Jiai ..

Thy this code in the script:

AA:

Load * Inline [

ID, Client, Check In, Check Out

1, James, 1/8/2014, 1/9/2014

2, Erica, 4/15/2014, 4/17/2014

3, Steve, 2/24/2014 , 2/25/2014

4, Taylor, 1/18/2014 , 1/21/2014

5, James, 1/12/2014 , 1/15/2014

6, Elizabeth, 3/5/2014, 3/6/2014

7, Taylor, 8/2/2014, 8/8/2014

8, Peter, 5/26/2014, 5/27/2014

9, Claire, 3/21/2014, 3/22/2014

];

Load

If (Client = Previous(Client), [Check In] - Previous([Check Out]),0) as DifferenceDays

Resident AA

Order by Client, [Check In] asc;

Drop Table AA;

Re: Frequent Customers

eduardo_sommer — Tue, 19 Aug 2014 18:56:48 GMT

Hi Gabriel

I think that zero is not a good option for the first check in, because it will have the same meaning as a Check in done in the same day as the last Check out. I would leave a null in this case.

If (Client = Previous(Client), [Check In] - Previous([Check Out])) as DifferenceDays

Eduardo

Re: Frequent Customers

Tue, 19 Aug 2014 18:57:28 GMT

Thank you

Re: Frequent Customers

Tue, 19 Aug 2014 19:18:47 GMT

Do you know how to sort the dates correctly? I have a client who have been in the system for years and they have 3 same dates but different years. The data is coming up 1/8/2013, 1/8/2012, 1/8/2014 for james.