I’m trying to build a pivot table/chart, and I’m hitting some roadblocks. I’m not sure if a PivotTable is the right way to solve this. I have a list of bugs that have an OpenDate and CloseDate. I’d like to build a chart that shows the number of Active bugs on a weekly basis. Given a date, I already know how to pull out the Sunday date (i.e. StartOfWeek), so that’s not an issue, but then it gets complicated.

There are 2 things I can’t figure out:
1) Not all weeks have active bugs. If I build a pivot table/chart using just the data in the spreadsheet, it will only show weeks which at least one bug was active during for that week. I’d like the chart to show the week even if there is no column to display. So Week 1 might have 10 active bugs, but Week 2 has 0, and Week 3 has 8. I’d like to show 3 data points.

2) How to calculate Active bug count? I need to dynamically determine if the bug was active or closed for a given week. I want to query my spreadsheet and return all the bugs where CloseDate is NULL or greater than StartOfWek (i.e. 1/2/2011).

The list of Weeks could be a list that I manage by hand or could be bounded by the min and max in the data, whatever's easiest.

Here’s an example of my data. The important points a (a) not all bugs are closed and (b) not all weeks have active bugs

BugId OpenDate CloseDate
1 12/28/2010 1/7/2011
2 1/6 1/8/2011
3 1/16/2011 1/18/2011
4 1/17

Can I do this with a PivotTable or is there something else I should look at?