Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Frequent Customers

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.

1 Solution

Accepted Solutions
Employee
Employee

Re: Frequent Customers

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;

7 Replies
Employee
Employee

Re: Frequent Customers

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])

jaimeaguilar
Valued Contributor II

Re: Frequent Customers

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

jaimeaguilar
Valued Contributor II

Re: Frequent Customers

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

Employee
Employee

Re: Frequent Customers

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;

eduardo_sommer
Valued Contributor

Re: Frequent Customers

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

Not applicable

Re: Frequent Customers

Thank you

Not applicable

Re: Frequent Customers

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.

Community Browser