7 Replies Latest reply: Aug 19, 2014 3:18 PM by Jia Li RSS

    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.

        • Re: Frequent Customers
          Jonathan Poole

          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
            Jaime Aguilar

            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
              Gabriel Araya

              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;