Pivot Table help!

[Marc Fleury]

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?

[bob777]

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

[Debra Dalgleish]

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

[Marc Fleury]

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 <[email protected]> wrote in
news:[email protected]:

> 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?
>
>

[Marc Fleury]

bob777 <[email protected]> wrote in
news:[email protected]:

>
> 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.

[Roger Govier]

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 <[email protected]> wrote in
> news:[email protected]:
>
>
>>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]

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 <[email protected]> wrote in
> news:[email protected]:
>
>
>>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

[Marc Fleury]

Debra Dalgleish <[email protected]> wrote in
news:[email protected]:

> 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]

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 <[email protected]> wrote in
> news:[email protected]:
>
>
>>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

[Marc Fleury]

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 <[email protected]> wrote in
news:[email protected]:

> 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 <[email protected]> wrote in
>> news:[email protected]:
>>
>>
>>>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.
>
>