Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 1
Default Is a PivotTable/PivotChart the right tool to use for this?

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?
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Link PivotChart to PivotTable, whilst hiding fields clifgriffin Excel Discussion (Misc queries) 0 March 3rd 09 08:03 PM
Duplicating PivotChart / Changing PivotChart data range BillG Charts and Charting in Excel 0 February 4th 09 09:56 PM
Make pivotchart formatting stick after pivottable refresh. larry garka Charts and Charting in Excel 3 June 5th 08 03:12 PM
Creating a PivotTable w/o selecting data in an existing PivotTable Damian Excel Discussion (Misc queries) 6 November 2nd 07 05:44 PM
PivotChart disassociation from PivotTable Sean McPoland Charts and Charting in Excel 0 July 4th 07 06:36 AM


All times are GMT +1. The time now is 11:14 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"