4 Replies Latest reply on Nov 29, 2016 2:37 PM by gareth.homayoun.0

    Incrementing a count based on 180 day intervals

    gareth.homayoun.0

      Hello everyone,

       

      I am trying to get a running total for the number of times a person visited a business based on 180 day intervals from their first visit.  For example if Person A visited:

       

      Person          Visit Date              Visit Count

      A                    1/1/2016               1

      A                    3/1/2016               1

      A                    5/1/2016               1

      A                    7/1/2016               2

      A                    9/1/2016               2

      A                    11/1/2016             2

      A                    1/1/2017               3

      B                    2/1/2016               1               <-- I would like this to reset based on the Person ID

      B                    4/1/2016               1

      B                    11/1/2016             2

       

      I have tried to use the following: if ATTR([Date])-LOOKUP(attr([Date]),-1) > 180 then 1 else 0 end

       

      However, when I try to set this to calculate based on the min date, it doesn't work correctly.  When I use the formula above, and set it to calculate based on the Person ID, it only calculates based on the last visit, not the initial visit compared to the present visit.

       

      Does anyone know how I can get the visit count to be accurately based on their initial visit for 180 day increments?

       

      Thanks,

       

      Gareth