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.

Not sure what you mean here. To expand on the description above. Say the data is:1 Mar a,b,c; 2Mar c,d; 3 Mar a,d; 4 Mar d; 5 Mar e. I would be looking for results of (distinct in the range): 1-3 Mar a,b,c,d; 2-4 Mar a,c,d; 3-5 Mar a,d,e. Since a single table can only hold unique ranges (as far as I can tell) I would need to create a table for each date, and then compare them. Beside the fact that this would require 60+ tables, the unique entry for each date is already accomplished by the SQL query. — user2984358
– user2984358, Commented Mar 23, 2016 at 17:27

jeffreyweir · Accepted Answer · 2016-03-24 00:39:05Z

0

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:

edited Mar 24, 2016 at 0:39

answered Mar 24, 2016 at 0:30

jeffreyweir

4,8841 gold badge19 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel Pivot Table - overlapping date ranges

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related