Frequent Customers

[#1]

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.

[JonnyPoole]

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]

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]

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

[GabrielAraya · Accepted solution]

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]

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

[#7]

Thank you

[#8]

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.