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

# Incrementing a count based on 180 day intervals

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

• ###### 1. Re: Incrementing a count based on 180 day intervals

Is this what you need?

• ###### 2. Re: Incrementing a count based on 180 day intervals

Hi Luciano,

Thank you for posting, this is very close to what I need.  I would just need it to show a 1 for the first 3 entries, then a 2 for the next 3, and then a 3 for the 1/1/2017 entry.  I'm basically trying to get a rolling count of how many 180 day periods they have visited.

Thanks,

Gareth

• ###### 3. Re: Incrementing a count based on 180 day intervals

Hey Gareth,

This was a tough one! I think I figured it out, though. I'm SURE there are simple/elegant ways to do this, but hopefully this will get you going in the right direction.

First, I created a "180 Day Period" field, which creates a date that is 180 days after the initial visit date. But, if the next visit date is >180 days after the initial, it creates a "new" date that is 360 days after the initial visit date. If the next visit date is >360 days after the initial visit date, it creates a "new" date that is 540 days after the initial date. And so on...

Next, I created a field that determines whether the current Visit date is the first date within a given 180 day period:

Next, I did a sum of the previously created field:

Lastly, I changed Sum of Min Visit Date to a running total:

Here is the final product:

Cheers,

Walt

1 of 1 people found this helpful
• ###### 4. Re: Incrementing a count based on 180 day intervals

Walt,

Thank you so much, this is awesome!  I've been trying to learn how to work with the level of detail functions, this is an excellent example of how helpful they can be.  I appreciate it!

Thanks again,

Gareth