software

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 quot;Datequot; 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
quot;Daysquot;, 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?


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: www.excelforum.com/member.php...oamp;userid=28504
View this thread: www.excelforum.com/showthread...hreadid=495104Put 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:
gt; I'm hoping someone can give some pointers to a pivot table newbie.
gt;
gt; I have a bunch of data in three colums: Player, Score, and Date. I want a
gt; pivot table that will show me the average Score for every Player, given an
gt; arbitrary Date range that I want to be able to specify.
gt;
gt; I'm partway there. I set up a pivot table, pull Player onto the Row Fields,
gt; and I put Score into the Data area.
gt;
gt; If I put Date into the Row Fields, and then group these by month, it almost
gt; gives me what I want -- I can click on quot;Datequot; on the pivot table and I have
gt; little boxes that I can check off if I just want to see the data for
gt; November, or whatever. But if I want to see just the data for, say, Nov 20-
gt; 26, I'm out of luck. If I right-click on Date, choose Grouping, and add
gt; quot;Daysquot;, it (again) almost does what I want, but it's really awkward because
gt; the days are now listed is a separate column than the months. It will
gt; become even more complicated once I start accumulating data for 2006.
gt;
gt; If, instead of dragging Date into the Row Fields, I put it into the Page
gt; Fields, it again ALMOST does what I want, but not quite.
gt;
gt; I have seen pivot tables where the page field has a Date button that opens
gt; up to show years, with little Plus signs next to them. When you click on
gt; the plus sign, it opens up a branch list that shows the quarters, or the
gt; months, each will little plus signs that open up further branches for
gt; individual days. Each of these levels can be selected with little check-
gt; boxes, so that if you want to see all the data from 2005, you just click
gt; the 2005 box. If, on the other hand, you want to see just November, then
gt; you open up the 2005 branch, and then just select November. You can also,
gt; if you want, select any combination of date ranges (for instance, all of
gt; 2004, plus November of 2005, plus the first three days of December).
gt;
gt; How the heck do I do that?--
Debra Dalgleish
Excel FAQ, Tips amp; Book List
www.contextures.com/tiptech.htmlI'm either slightly misunderstanding, or it still doesn't work.

If I put in the number of days in my range in the quot;Number of Daysquot; 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 gt; wrote in
:

gt; Put the Date field into the row area, and group by Days only.
gt; Enter your start and end date in the Grouping dialog box.
gt; For the number of days, you can enter the number of days in your
gt; range, or a big number, if you aren't sure how many days are in the
gt; selected range. Then, in the Date field dropdown, click Select All, to
gt; uncheck all, then select the item with your date range.
gt;
gt; Marc Fleury wrote:
gt;gt; I'm hoping someone can give some pointers to a pivot table newbie.
gt;gt;
gt;gt; I have a bunch of data in three colums: Player, Score, and Date. I
gt;gt; want a pivot table that will show me the average Score for every
gt;gt; Player, given an arbitrary Date range that I want to be able to
gt;gt; specify.
gt;gt;
gt;gt; I'm partway there. I set up a pivot table, pull Player onto the Row
gt;gt; Fields, and I put Score into the Data area.
gt;gt;
gt;gt; If I put Date into the Row Fields, and then group these by month, it
gt;gt; almost gives me what I want -- I can click on quot;Datequot; on the pivot
gt;gt; table and I have little boxes that I can check off if I just want to
gt;gt; see the data for November, or whatever. But if I want to see just the
gt;gt; data for, say, Nov 20- 26, I'm out of luck. If I right-click on Date,
gt;gt; choose Grouping, and add quot;Daysquot;, it (again) almost does what I want,
gt;gt; but it's really awkward because the days are now listed is a separate
gt;gt; column than the months. It will become even more complicated once I
gt;gt; start accumulating data for 2006.
gt;gt;
gt;gt; If, instead of dragging Date into the Row Fields, I put it into the
gt;gt; Page Fields, it again ALMOST does what I want, but not quite.
gt;gt;
gt;gt; I have seen pivot tables where the page field has a Date button that
gt;gt; opens up to show years, with little Plus signs next to them. When you
gt;gt; click on the plus sign, it opens up a branch list that shows the
gt;gt; quarters, or the months, each will little plus signs that open up
gt;gt; further branches for individual days. Each of these levels can be
gt;gt; selected with little check- boxes, so that if you want to see all the
gt;gt; data from 2005, you just click the 2005 box. If, on the other hand,
gt;gt; you want to see just November, then you open up the 2005 branch, and
gt;gt; then just select November. You can also, if you want, select any
gt;gt; combination of date ranges (for instance, all of 2004, plus November
gt;gt; of 2005, plus the first three days of December).
gt;gt;
gt;gt; How the heck do I do that?
gt;
gt;bob777 gt; wrote in
:

gt;
gt; Hi,
gt; I suggest you abandon the pivot table and use the sumproduct function.
gt;
gt; Then you can put the names of the players in col A, put your required
gt; dates in 2 convenient cells, and then use sumproduct to extract the
gt; total scores achieved by each player within your date ranges.
gt;
gt; In a separate table, use countif to count the NUMBER of scores within
gt; the date range, and then finally print the averages in a separate
gt; table. If you are happy to publish your e-mail address, I will send
gt; 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.

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
YearsQuarters MonthsDate PlayerTotal
2005Qtr1 Jan01-JanAAA2
CCC1
DDD2
08-JanBBB1
CCC3
15-JanAAA1
29-JanAAA4
Feb01-FebAAA4
BBB3
07-FebAAA1
08-FebCCC3
15-FebAAA1
BBB4
CCC2
DDD5
22-FebDDD2
Grand Total39

Does this solve the problem?Regards

Roger GovierMarc Fleury wrote:
gt; I'm either slightly misunderstanding, or it still doesn't work.
gt;
gt; If I put in the number of days in my range in the quot;Number of Daysquot; box,
gt; what happens when I click the Date dropdown is that I only have 3
gt; choices to view: data before my range, data after my range (both of
gt; which will show nothing), or the entire range. I have no ability to
gt; select, say, just February.
gt;
gt;
gt; --
gt; Marc.
gt;
gt;
gt; Debra Dalgleish gt; wrote in
gt; :
gt;
gt;
gt;gt;Put the Date field into the row area, and group by Days only.
gt;gt;Enter your start and end date in the Grouping dialog box.
gt;gt;For the number of days, you can enter the number of days in your
gt;gt;range, or a big number, if you aren't sure how many days are in the
gt;gt;selected range. Then, in the Date field dropdown, click Select All, to
gt;gt;uncheck all, then select the item with your date range.
gt;gt;
gt;gt;Marc Fleury wrote:
gt;gt;
gt;gt;gt;I'm hoping someone can give some pointers to a pivot table newbie.
gt;gt;gt;
gt;gt;gt;I have a bunch of data in three colums: Player, Score, and Date. I
gt;gt;gt;want a pivot table that will show me the average Score for every
gt;gt;gt;Player, given an arbitrary Date range that I want to be able to
gt;gt;gt;specify.
gt;gt;gt;
gt;gt;gt;I'm partway there. I set up a pivot table, pull Player onto the Row
gt;gt;gt;Fields, and I put Score into the Data area.
gt;gt;gt;
gt;gt;gt;If I put Date into the Row Fields, and then group these by month, it
gt;gt;gt;almost gives me what I want -- I can click on quot;Datequot; on the pivot
gt;gt;gt;table and I have little boxes that I can check off if I just want to
gt;gt;gt;see the data for November, or whatever. But if I want to see just the
gt;gt;gt;data for, say, Nov 20- 26, I'm out of luck. If I right-click on Date,
gt;gt;gt;choose Grouping, and add quot;Daysquot;, it (again) almost does what I want,
gt;gt;gt;but it's really awkward because the days are now listed is a separate
gt;gt;gt;column than the months. It will become even more complicated once I
gt;gt;gt;start accumulating data for 2006.
gt;gt;gt;
gt;gt;gt;If, instead of dragging Date into the Row Fields, I put it into the
gt;gt;gt;Page Fields, it again ALMOST does what I want, but not quite.
gt;gt;gt;
gt;gt;gt;I have seen pivot tables where the page field has a Date button that
gt;gt;gt;opens up to show years, with little Plus signs next to them. When you
gt;gt;gt;click on the plus sign, it opens up a branch list that shows the
gt;gt;gt;quarters, or the months, each will little plus signs that open up
gt;gt;gt;further branches for individual days. Each of these levels can be
gt;gt;gt;selected with little check- boxes, so that if you want to see all the
gt;gt;gt;data from 2005, you just click the 2005 box. If, on the other hand,
gt;gt;gt;you want to see just November, then you open up the 2005 branch, and
gt;gt;gt;then just select November. You can also, if you want, select any
gt;gt;gt;combination of date ranges (for instance, all of 2004, plus November
gt;gt;gt;of 2005, plus the first three days of December).
gt;gt;gt;
gt;gt;gt;How the heck do I do that?
gt;gt;
gt;gt;
gt;

In your original post you said:
quot;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.quot;

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:
gt; I'm either slightly misunderstanding, or it still doesn't work.
gt;
gt; If I put in the number of days in my range in the quot;Number of Daysquot; box,
gt; what happens when I click the Date dropdown is that I only have 3
gt; choices to view: data before my range, data after my range (both of
gt; which will show nothing), or the entire range. I have no ability to
gt; select, say, just February.
gt;
gt;
gt; --
gt; Marc.
gt;
gt;
gt; Debra Dalgleish gt; wrote in
gt; :
gt;
gt;
gt;gt;Put the Date field into the row area, and group by Days only.
gt;gt;Enter your start and end date in the Grouping dialog box.
gt;gt;For the number of days, you can enter the number of days in your
gt;gt;range, or a big number, if you aren't sure how many days are in the
gt;gt;selected range. Then, in the Date field dropdown, click Select All, to
gt;gt;uncheck all, then select the item with your date range.
gt;gt;
gt;gt;Marc Fleury wrote:
gt;gt;
gt;gt;gt;I'm hoping someone can give some pointers to a pivot table newbie.
gt;gt;gt;
gt;gt;gt;I have a bunch of data in three colums: Player, Score, and Date. I
gt;gt;gt;want a pivot table that will show me the average Score for every
gt;gt;gt;Player, given an arbitrary Date range that I want to be able to
gt;gt;gt;specify.
gt;gt;gt;
gt;gt;gt;I'm partway there. I set up a pivot table, pull Player onto the Row
gt;gt;gt;Fields, and I put Score into the Data area.
gt;gt;gt;
gt;gt;gt;If I put Date into the Row Fields, and then group these by month, it
gt;gt;gt;almost gives me what I want -- I can click on quot;Datequot; on the pivot
gt;gt;gt;table and I have little boxes that I can check off if I just want to
gt;gt;gt;see the data for November, or whatever. But if I want to see just the
gt;gt;gt;data for, say, Nov 20- 26, I'm out of luck. If I right-click on Date,
gt;gt;gt;choose Grouping, and add quot;Daysquot;, it (again) almost does what I want,
gt;gt;gt;but it's really awkward because the days are now listed is a separate
gt;gt;gt;column than the months. It will become even more complicated once I
gt;gt;gt;start accumulating data for 2006.
gt;gt;gt;
gt;gt;gt;If, instead of dragging Date into the Row Fields, I put it into the
gt;gt;gt;Page Fields, it again ALMOST does what I want, but not quite.
gt;gt;gt;
gt;gt;gt;I have seen pivot tables where the page field has a Date button that
gt;gt;gt;opens up to show years, with little Plus signs next to them. When you
gt;gt;gt;click on the plus sign, it opens up a branch list that shows the
gt;gt;gt;quarters, or the months, each will little plus signs that open up
gt;gt;gt;further branches for individual days. Each of these levels can be
gt;gt;gt;selected with little check- boxes, so that if you want to see all the
gt;gt;gt;data from 2005, you just click the 2005 box. If, on the other hand,
gt;gt;gt;you want to see just November, then you open up the 2005 branch, and
gt;gt;gt;then just select November. You can also, if you want, select any
gt;gt;gt;combination of date ranges (for instance, all of 2004, plus November
gt;gt;gt;of 2005, plus the first three days of December).
gt;gt;gt;
gt;gt;gt;How the heck do I do that?
gt;gt;
gt;gt;
gt;--
Debra Dalgleish
Excel FAQ, Tips amp; Book List
www.contextures.com/tiptech.htmlDebra Dalgleish gt; wrote in
:

gt; In your original post you said:
gt; quot;I want a pivot table that will show me the average Score for every
gt; Player, given an arbitrary Date range that I want to be able to
gt; specify.quot;
gt;
gt; 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:

quot;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?quot;
--
Marc.

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:
gt; Debra Dalgleish gt; wrote in
gt; :
gt;
gt;
gt;gt;In your original post you said:
gt;gt;quot;I want a pivot table that will show me the average Score for every
gt;gt;Player, given an arbitrary Date range that I want to be able to
gt;gt;specify.quot;
gt;gt;
gt;gt;What I suggested does that.
gt;
gt;
gt;
gt; Ah. Now I understand what you mean.
gt;
gt; I guess I should have drawn a distinction between what I, as the creator
gt; of the Pivot Table, can do, and what the end-user can do. In the last
gt; part of my original post, I was more explicit about my needs. The user
gt; should be able to get the data that s/he wants just by using drop-down
gt; boxes. The USER ought to be able to select an arbitrary dates and date
gt; ranges to view.
gt;
gt; Here, again, is the more detailed explanation of what I need:
gt;
gt; quot;I have seen pivot tables where the page field has a Date button that
gt; opens up to show years, with little Plus signs next to them. When you
gt; click on the plus sign, it opens up a branch list that shows the
gt; quarters, or the months, each will little plus signs that open up
gt; further branches for individual days. Each of these levels can be
gt; selected with little check- boxes, so that if you want to see all the
gt; data from 2005, you just click the 2005 box. If, on the other hand, you
gt; want to see just November, then you open up the 2005 branch, and then
gt; just select November. You can also, if you want, select any combination
gt; of date ranges (for instance, all of 2004, plus November of 2005, plus
gt; the first three days of December).
gt;
gt; How the heck do I do that?quot;
gt;
gt;
gt;
gt; --
gt; Marc.--
Debra Dalgleish
Excel FAQ, Tips amp; Book List
www.contextures.com/tiptech.htmlMuch appreciated.

With that info, I searched through the excel help files for OLAP
database, found this
office.microsoft.com/en-us/as...127121033.aspx
(quot;Creating data cubes using Excel and text filesquot;). 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 gt; wrote in
:

gt; Those pivot tables are based on OLAP databases, with fields in levels.
gt; The same feature can't be created in a pivot table based on a
gt; different data source type.
gt;
gt; Marc Fleury wrote:
gt;gt; Debra Dalgleish gt; wrote in
gt;gt; :
gt;gt;
gt;gt;
gt;gt;gt;In your original post you said:
gt;gt;gt;quot;I want a pivot table that will show me the average Score for every
gt;gt;gt;Player, given an arbitrary Date range that I want to be able to
gt;gt;gt;specify.quot;
gt;gt;gt;
gt;gt;gt;What I suggested does that.
gt;gt;
gt;gt;
gt;gt;
gt;gt; Ah. Now I understand what you mean.
gt;gt;
gt;gt; I guess I should have drawn a distinction between what I, as the
gt;gt; creator of the Pivot Table, can do, and what the end-user can do. In
gt;gt; the last part of my original post, I was more explicit about my
gt;gt; needs. The user should be able to get the data that s/he wants just
gt;gt; by using drop-down boxes. The USER ought to be able to select an
gt;gt; arbitrary dates and date ranges to view.
gt;gt;
gt;gt; Here, again, is the more detailed explanation of what I need:
gt;gt;
gt;gt; quot;I have seen pivot tables where the page field has a Date button that
gt;gt; opens up to show years, with little Plus signs next to them. When you
gt;gt; click on the plus sign, it opens up a branch list that shows the
gt;gt; quarters, or the months, each will little plus signs that open up
gt;gt; further branches for individual days. Each of these levels can be
gt;gt; selected with little check- boxes, so that if you want to see all the
gt;gt; data from 2005, you just click the 2005 box. If, on the other hand,
gt;gt; you want to see just November, then you open up the 2005 branch, and
gt;gt; then just select November. You can also, if you want, select any
gt;gt; combination of date ranges (for instance, all of 2004, plus November
gt;gt; of 2005, plus the first three days of December).
gt;gt;
gt;gt; How the heck do I do that?quot;
gt;gt;
gt;gt;
gt;gt;
gt;gt; --
gt;gt; Marc.
gt;
gt;