Excel Pivot Table help

From Visual Basic to GNU C, this is the place to talk programming.

Moderators: SecretSquirrel, just brew it!

Excel Pivot Table help

Postposted on Fri Jan 18, 2013 2:12 pm

Hi all,

I'm pretty decent with excel, but I'm running into a problem that is stumping me right now.

I am trying to determine bug fix rates for my team. I've got two sets of data, Set A= count of bugs opened per day, Set B= count of bugs closed per day

I want a chart showing for a given day, the number of bugs opened and closed for a given team member.

I'm trying to use a Pivot table, but the problem is that unless I manually group the dates, I don't see the counts correctly. Right now, I can easily see the breakdown for either open or close, but not both for the same day.

I've got the sets laid out side by side (columns A-C is set A, columns D-F is set B). The pivot table reads across the entire row, so if I select a date from Set A, then it will only count Set B if it is on the same row, but it ignores the date in set B. If I try and filter by set B, it shows the date in Set B, but still counts it for the date on set A.

I guess what I'm trying to do is align the number of bugs opened and closed for a given date, and then break it out by team member. I can do it in two pivot tables, but then how do I chart it on the same graph?
druidcent
Minister of Gerbil Affairs
 
Posts: 2072
Joined: Wed Aug 07, 2002 7:55 pm
Location: Earth, Sol, Milky Way

Re: Excel Pivot Table help

Postposted on Fri Jan 18, 2013 8:01 pm

Rather than fixing your problem in the pivot table generated from the two data tables, I might be tempted to create a third data table or modify one of the existing ones to automatically blend the two existing data sets. You can use the VLOOKUP() function to pull data from the existing data table that matches the date in your new table.
http://www.techonthenet.com/excel/formulas/vlookup.php
i7-4770K, H70, Gryphon Z87, 16 GiB, R9-290, SSD, 2 HD, Blu-ray, SB ZX, TJ08-E, SS-660XP², 3007WFP+2001FP, RK-9000BR, MX518
JustAnEngineer
Gerbil God
Gold subscriber
 
 
Posts: 15337
Joined: Sat Jan 26, 2002 7:00 pm
Location: The Heart of Dixie

Re: Excel Pivot Table help

Postposted on Fri Jan 18, 2013 8:06 pm

That's a good idea.. question though, VLookup returns the first cell that it finds, so if I've got multiple dates how would that match up? Also, I'm not really trying to blend the data.. what I'm looking for is a way to show for a given date, how many bugs a team member closed, and the how many bugs were filed against them.
druidcent
Minister of Gerbil Affairs
 
Posts: 2072
Joined: Wed Aug 07, 2002 7:55 pm
Location: Earth, Sol, Milky Way

Re: Excel Pivot Table help

Postposted on Sun Jan 20, 2013 11:15 pm

Sumifs() and countifs() are your friend. I'd agree with the 3rd table. That's how I'd do it myself.
Usacomp2k3
Gerbil God
 
Posts: 21281
Joined: Thu Apr 01, 2004 4:53 pm
Location: Orlando, FL


Return to Developer's Den

Who is online

Users browsing this forum: No registered users and 2 guests