Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table help!
I'm hoping someone can give some pointers to a pivot table newbie.
I have a bunch of data in three colums: Player, Score, and Date. I want a pivot table that will show me the average Score for every Player, given an arbitrary Date range that I want to be able to specify. I'm partway there. I set up a pivot table, pull Player onto the Row Fields, and I put Score into the Data area. If I put Date into the Row Fields, and then group these by month, it almost gives me what I want -- I can click on "Date" on the pivot table and I have little boxes that I can check off if I just want to see the data for November, or whatever. But if I want to see just the data for, say, Nov 20- 26, I'm out of luck. If I right-click on Date, choose Grouping, and add "Days", it (again) almost does what I want, but it's really awkward because the days are now listed is a separate column than the months. It will become even more complicated once I start accumulating data for 2006. If, instead of dragging Date into the Row Fields, I put it into the Page Fields, it again ALMOST does what I want, but not quite. I have seen pivot tables where the page field has a Date button that opens up to show years, with little Plus signs next to them. When you click on the plus sign, it opens up a branch list that shows the quarters, or the months, each will little plus signs that open up further branches for individual days. Each of these levels can be selected with little check- boxes, so that if you want to see all the data from 2005, you just click the 2005 box. If, on the other hand, you want to see just November, then you open up the 2005 branch, and then just select November. You can also, if you want, select any combination of date ranges (for instance, all of 2004, plus November of 2005, plus the first three days of December). How the heck do I do that? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table help!
Hi, I suggest you abandon the pivot table and use the sumproduct function. Then you can put the names of the players in col A, put your required dates in 2 convenient cells, and then use sumproduct to extract the total scores achieved by each player within your date ranges. In a separate table, use countif to count the NUMBER of scores within the date range, and then finally print the averages in a separate table. If you are happy to publish your e-mail address, I will send you a simple spreadsheet demonstrating this. Bobf -- bob777 ------------------------------------------------------------------------ bob777's Profile: http://www.excelforum.com/member.php...o&userid=28504 View this thread: http://www.excelforum.com/showthread...hreadid=495104 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table help!
Put the Date field into the row area, and group by Days only.
Enter your start and end date in the Grouping dialog box. For the number of days, you can enter the number of days in your range, or a big number, if you aren't sure how many days are in the selected range. Then, in the Date field dropdown, click Select All, to uncheck all, then select the item with your date range. Marc Fleury wrote: I'm hoping someone can give some pointers to a pivot table newbie. I have a bunch of data in three colums: Player, Score, and Date. I want a pivot table that will show me the average Score for every Player, given an arbitrary Date range that I want to be able to specify. I'm partway there. I set up a pivot table, pull Player onto the Row Fields, and I put Score into the Data area. If I put Date into the Row Fields, and then group these by month, it almost gives me what I want -- I can click on "Date" on the pivot table and I have little boxes that I can check off if I just want to see the data for November, or whatever. But if I want to see just the data for, say, Nov 20- 26, I'm out of luck. If I right-click on Date, choose Grouping, and add "Days", it (again) almost does what I want, but it's really awkward because the days are now listed is a separate column than the months. It will become even more complicated once I start accumulating data for 2006. If, instead of dragging Date into the Row Fields, I put it into the Page Fields, it again ALMOST does what I want, but not quite. I have seen pivot tables where the page field has a Date button that opens up to show years, with little Plus signs next to them. When you click on the plus sign, it opens up a branch list that shows the quarters, or the months, each will little plus signs that open up further branches for individual days. Each of these levels can be selected with little check- boxes, so that if you want to see all the data from 2005, you just click the 2005 box. If, on the other hand, you want to see just November, then you open up the 2005 branch, and then just select November. You can also, if you want, select any combination of date ranges (for instance, all of 2004, plus November of 2005, plus the first three days of December). How the heck do I do that? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table help!
I'm either slightly misunderstanding, or it still doesn't work.
If I put in the number of days in my range in the "Number of Days" box, what happens when I click the Date dropdown is that I only have 3 choices to view: data before my range, data after my range (both of which will show nothing), or the entire range. I have no ability to select, say, just February. -- Marc. Debra Dalgleish wrote in : Put the Date field into the row area, and group by Days only. Enter your start and end date in the Grouping dialog box. For the number of days, you can enter the number of days in your range, or a big number, if you aren't sure how many days are in the selected range. Then, in the Date field dropdown, click Select All, to uncheck all, then select the item with your date range. Marc Fleury wrote: I'm hoping someone can give some pointers to a pivot table newbie. I have a bunch of data in three colums: Player, Score, and Date. I want a pivot table that will show me the average Score for every Player, given an arbitrary Date range that I want to be able to specify. I'm partway there. I set up a pivot table, pull Player onto the Row Fields, and I put Score into the Data area. If I put Date into the Row Fields, and then group these by month, it almost gives me what I want -- I can click on "Date" on the pivot table and I have little boxes that I can check off if I just want to see the data for November, or whatever. But if I want to see just the data for, say, Nov 20- 26, I'm out of luck. If I right-click on Date, choose Grouping, and add "Days", it (again) almost does what I want, but it's really awkward because the days are now listed is a separate column than the months. It will become even more complicated once I start accumulating data for 2006. If, instead of dragging Date into the Row Fields, I put it into the Page Fields, it again ALMOST does what I want, but not quite. I have seen pivot tables where the page field has a Date button that opens up to show years, with little Plus signs next to them. When you click on the plus sign, it opens up a branch list that shows the quarters, or the months, each will little plus signs that open up further branches for individual days. Each of these levels can be selected with little check- boxes, so that if you want to see all the data from 2005, you just click the 2005 box. If, on the other hand, you want to see just November, then you open up the 2005 branch, and then just select November. You can also, if you want, select any combination of date ranges (for instance, all of 2004, plus November of 2005, plus the first three days of December). How the heck do I do that? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table help!
bob777 wrote in
: Hi, I suggest you abandon the pivot table and use the sumproduct function. Then you can put the names of the players in col A, put your required dates in 2 convenient cells, and then use sumproduct to extract the total scores achieved by each player within your date ranges. In a separate table, use countif to count the NUMBER of scores within the date range, and then finally print the averages in a separate table. If you are happy to publish your e-mail address, I will send you a simple spreadsheet demonstrating this. I considered this (and may still do something like that if I can't find the proper solution with the pivot tables), but the pivot tables have several advantages that I really like -- Players will be able to double click on their score summary in the table to drill down to the data of their actual games (which show a lot more detail than the pivot table provides). Also, if I was able to implement the table the way I want, a player could decide to check their scores for games played on Mondays (by checking just those days from the drop-down box) and then comparing these scores to games played on Fridays. Most of the people who will be using the tool are familiar with Pivot Tables (reading them, not creating them), so although designing my own tool to extract the desired data is probably feasible, it's not ideal. -- Marc. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table help!
Hi Marc
With Date as a row field, before Player as a row field, try Grouping Date by Year, and Quarter, and Month, and Day. You should then see something like the following (hope it doesn't go all over the place when posting). You can then select whichever year you want in the Years dropdown etc. Sum of Score Years Quarters Months Date Player Total 2005 Qtr1 Jan 01-Jan AAA 2 CCC 1 DDD 2 08-Jan BBB 1 CCC 3 15-Jan AAA 1 29-Jan AAA 4 Feb 01-Feb AAA 4 BBB 3 07-Feb AAA 1 08-Feb CCC 3 15-Feb AAA 1 BBB 4 CCC 2 DDD 5 22-Feb DDD 2 Grand Total 39 Does this solve the problem? Regards Roger Govier Marc Fleury wrote: I'm either slightly misunderstanding, or it still doesn't work. If I put in the number of days in my range in the "Number of Days" box, what happens when I click the Date dropdown is that I only have 3 choices to view: data before my range, data after my range (both of which will show nothing), or the entire range. I have no ability to select, say, just February. -- Marc. Debra Dalgleish wrote in : Put the Date field into the row area, and group by Days only. Enter your start and end date in the Grouping dialog box. For the number of days, you can enter the number of days in your range, or a big number, if you aren't sure how many days are in the selected range. Then, in the Date field dropdown, click Select All, to uncheck all, then select the item with your date range. Marc Fleury wrote: I'm hoping someone can give some pointers to a pivot table newbie. I have a bunch of data in three colums: Player, Score, and Date. I want a pivot table that will show me the average Score for every Player, given an arbitrary Date range that I want to be able to specify. I'm partway there. I set up a pivot table, pull Player onto the Row Fields, and I put Score into the Data area. If I put Date into the Row Fields, and then group these by month, it almost gives me what I want -- I can click on "Date" on the pivot table and I have little boxes that I can check off if I just want to see the data for November, or whatever. But if I want to see just the data for, say, Nov 20- 26, I'm out of luck. If I right-click on Date, choose Grouping, and add "Days", it (again) almost does what I want, but it's really awkward because the days are now listed is a separate column than the months. It will become even more complicated once I start accumulating data for 2006. If, instead of dragging Date into the Row Fields, I put it into the Page Fields, it again ALMOST does what I want, but not quite. I have seen pivot tables where the page field has a Date button that opens up to show years, with little Plus signs next to them. When you click on the plus sign, it opens up a branch list that shows the quarters, or the months, each will little plus signs that open up further branches for individual days. Each of these levels can be selected with little check- boxes, so that if you want to see all the data from 2005, you just click the 2005 box. If, on the other hand, you want to see just November, then you open up the 2005 branch, and then just select November. You can also, if you want, select any combination of date ranges (for instance, all of 2004, plus November of 2005, plus the first three days of December). How the heck do I do that? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table help!
In your original post you said:
"I want a pivot table that will show me the average Score for every Player, given an arbitrary Date range that I want to be able to specify." What I suggested does that. The start and end dates of the arbitrary date range are entered in the Grouping dialog box. Dates before and after are hidden. To see a different date range, e.g. November, open the Grouping box, and enter the start and end dates for that range. Marc Fleury wrote: I'm either slightly misunderstanding, or it still doesn't work. If I put in the number of days in my range in the "Number of Days" box, what happens when I click the Date dropdown is that I only have 3 choices to view: data before my range, data after my range (both of which will show nothing), or the entire range. I have no ability to select, say, just February. -- Marc. Debra Dalgleish wrote in : Put the Date field into the row area, and group by Days only. Enter your start and end date in the Grouping dialog box. For the number of days, you can enter the number of days in your range, or a big number, if you aren't sure how many days are in the selected range. Then, in the Date field dropdown, click Select All, to uncheck all, then select the item with your date range. Marc Fleury wrote: I'm hoping someone can give some pointers to a pivot table newbie. I have a bunch of data in three colums: Player, Score, and Date. I want a pivot table that will show me the average Score for every Player, given an arbitrary Date range that I want to be able to specify. I'm partway there. I set up a pivot table, pull Player onto the Row Fields, and I put Score into the Data area. If I put Date into the Row Fields, and then group these by month, it almost gives me what I want -- I can click on "Date" on the pivot table and I have little boxes that I can check off if I just want to see the data for November, or whatever. But if I want to see just the data for, say, Nov 20- 26, I'm out of luck. If I right-click on Date, choose Grouping, and add "Days", it (again) almost does what I want, but it's really awkward because the days are now listed is a separate column than the months. It will become even more complicated once I start accumulating data for 2006. If, instead of dragging Date into the Row Fields, I put it into the Page Fields, it again ALMOST does what I want, but not quite. I have seen pivot tables where the page field has a Date button that opens up to show years, with little Plus signs next to them. When you click on the plus sign, it opens up a branch list that shows the quarters, or the months, each will little plus signs that open up further branches for individual days. Each of these levels can be selected with little check- boxes, so that if you want to see all the data from 2005, you just click the 2005 box. If, on the other hand, you want to see just November, then you open up the 2005 branch, and then just select November. You can also, if you want, select any combination of date ranges (for instance, all of 2004, plus November of 2005, plus the first three days of December). How the heck do I do that? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table help!
Debra Dalgleish wrote in
: In your original post you said: "I want a pivot table that will show me the average Score for every Player, given an arbitrary Date range that I want to be able to specify." What I suggested does that. Ah. Now I understand what you mean. I guess I should have drawn a distinction between what I, as the creator of the Pivot Table, can do, and what the end-user can do. In the last part of my original post, I was more explicit about my needs. The user should be able to get the data that s/he wants just by using drop-down boxes. The USER ought to be able to select an arbitrary dates and date ranges to view. Here, again, is the more detailed explanation of what I need: "I have seen pivot tables where the page field has a Date button that opens up to show years, with little Plus signs next to them. When you click on the plus sign, it opens up a branch list that shows the quarters, or the months, each will little plus signs that open up further branches for individual days. Each of these levels can be selected with little check- boxes, so that if you want to see all the data from 2005, you just click the 2005 box. If, on the other hand, you want to see just November, then you open up the 2005 branch, and then just select November. You can also, if you want, select any combination of date ranges (for instance, all of 2004, plus November of 2005, plus the first three days of December). How the heck do I do that?" -- Marc. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table help!
Those pivot tables are based on OLAP databases, with fields in levels.
The same feature can't be created in a pivot table based on a different data source type. Marc Fleury wrote: Debra Dalgleish wrote in : In your original post you said: "I want a pivot table that will show me the average Score for every Player, given an arbitrary Date range that I want to be able to specify." What I suggested does that. Ah. Now I understand what you mean. I guess I should have drawn a distinction between what I, as the creator of the Pivot Table, can do, and what the end-user can do. In the last part of my original post, I was more explicit about my needs. The user should be able to get the data that s/he wants just by using drop-down boxes. The USER ought to be able to select an arbitrary dates and date ranges to view. Here, again, is the more detailed explanation of what I need: "I have seen pivot tables where the page field has a Date button that opens up to show years, with little Plus signs next to them. When you click on the plus sign, it opens up a branch list that shows the quarters, or the months, each will little plus signs that open up further branches for individual days. Each of these levels can be selected with little check- boxes, so that if you want to see all the data from 2005, you just click the 2005 box. If, on the other hand, you want to see just November, then you open up the 2005 branch, and then just select November. You can also, if you want, select any combination of date ranges (for instance, all of 2004, plus November of 2005, plus the first three days of December). How the heck do I do that?" -- Marc. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table help!
Much appreciated.
With that info, I searched through the excel help files for OLAP database, found this http://office.microsoft.com/en-us/as...127121033.aspx ("Creating data cubes using Excel and text files"). Probably the long way around, but I was able to turn my data into an OLAP cube, and the Date field automatically had levels when I pulled it to the Dimensions pane. So now I have exactly the drop-down box for Dates that I needed. (Now I just have to figure out how to create the rest of the Pivot Table from the data cube!) Thanks again. -- Marc. Debra Dalgleish wrote in : Those pivot tables are based on OLAP databases, with fields in levels. The same feature can't be created in a pivot table based on a different data source type. Marc Fleury wrote: Debra Dalgleish wrote in : In your original post you said: "I want a pivot table that will show me the average Score for every Player, given an arbitrary Date range that I want to be able to specify." What I suggested does that. Ah. Now I understand what you mean. I guess I should have drawn a distinction between what I, as the creator of the Pivot Table, can do, and what the end-user can do. In the last part of my original post, I was more explicit about my needs. The user should be able to get the data that s/he wants just by using drop-down boxes. The USER ought to be able to select an arbitrary dates and date ranges to view. Here, again, is the more detailed explanation of what I need: "I have seen pivot tables where the page field has a Date button that opens up to show years, with little Plus signs next to them. When you click on the plus sign, it opens up a branch list that shows the quarters, or the months, each will little plus signs that open up further branches for individual days. Each of these levels can be selected with little check- boxes, so that if you want to see all the data from 2005, you just click the 2005 box. If, on the other hand, you want to see just November, then you open up the 2005 branch, and then just select November. You can also, if you want, select any combination of date ranges (for instance, all of 2004, plus November of 2005, plus the first three days of December). How the heck do I do that?" -- Marc. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pivot table created from another pivot table | Excel Worksheet Functions | |||
Change Data In Pivot Table | New Users to Excel | |||
Pivot Table - Multiple consolidation Range | Excel Worksheet Functions | |||
Pivot table, dynamic data formula | Excel Discussion (Misc queries) | |||
Pivot Table Problems | Excel Discussion (Misc queries) |