Excel Pivot Table - overlapping date ranges

Question

I have a set of data with dates associated:
1 Mar a
2 Mar b
3 Mar c
4 Mar d
5 Mar e
6 Mar f

While a pivot table will allow date ranges (groupings) to be set up, these are only distinct ranges:
1-3 Mar
4-6 Mar

What I would like to be able to do is have the groupings overlap:
1-3 Mar
2-4 Mar
3-5 Mar
4-6 Mar

Is this possible for Excel 2010. The whole point of this is to find the number of distinct entries (a,b,c,d,e,f) in a rolling date range as some entries will fall into and out of the range at different days.

Answer 1

You're going to have to amend the SQL query to do this. I believe you'll have to use a couple of UNION ALL queries to duplicate the 'main' query twice. In the first UNION ALL bit, subtract one from the date. In the second UNION ALL bit, subtract 2 from the date. The resulting data - if returned as a Table, and using your sample data as an example - would look like this:

When you Pivot it, put Date in the ROWS pane, filter out the first two dates for the 28th and 29th of Feb, put Item in the COLUMNS pane, and put Item again in the Values area, like so:

No need to do any grouping: it is effectively already grouped how you want it on account of those UNION ALL duplicates. Ignore the values of those numbers...it is the presence or absence of a number that is of interest. In fact, you can even apply a custom number format of "Yes";;; that will return the following: