How do you break up a date range into intervals?

[#1]

Hi. I have a pretty simple chart that looks like:

The X axis is a count of products; the Y axis is the Release Date for the product(s). The date range for this chart is customizable and is determined by a set of calendar objects (so for this example, I set a start date of 2015-06-12 and an end date of 2015-07-09, but the user could obviously choose any date range). Also notice that there isn't at least one product release for every date in b/t the selected date range.

What I'd like to do is aggregate the product counts into two week intervals rather than by individual date, and I'd like the intervals to be two weeks forward and back from the current date. So, let's say today is 2015-06-25. The above chart would then have two bars (1st interval: 2015-06-12 to 2015-06-25 and the total # products = 9; 2nd interval: 2015-06-26 to 2015-07-09 and the total # products = 2).

In addition, since the date range is customizable, this function would have to account for partial intervals. For example, if today is 2015-06-25, but someone selects a date range of 2015-06-04 to 2015-07-15, then there would be four intervals:

Interval 1: 2015-06-04 to 2015-06-11

Interval 2: 2015-06-12 to 2015-06-25

Interval 3: 2015-06-26 to 2015-07-09

Interval 4: 2015-07-10 to 2015-07-15

(each interval contains exactly 14 (or less) days and there are no overlap of any days).

Does anyone know how to do this? Thanks in advance for any help.

-Kenny

[swuehl]

Try something like

=aggr( Date(today()+floor(Date-today(),14)),Date)

This should return a classification based on interval start dates.

edit:

You can also create the intervals in your script, if you want, if the script runs daily

[MarcoWedel · Accepted solution]

Hi,

some similar solution could be:

hope this helps

regards

Marco

[#4]

A very sincere "thanks" to both swuehl and Marco for their quick and detailed responses. I tested Marco's suggestion with my app, and it seems to work just as I want. You saved me many hours, if not days, trying to figure this out! Thanks again.

-Kenny