Pivot table grouping

This has got to be a common problem, but I am not finding the solution. I
import sales data via an xml list. I created the pivot table on the series
of dates the sales happened. It naturally groups the data with summary
columns for the number of sales on each date, just what I would expect.

Now I want to that those figures additionally grouped by month, then year.
Though tedious, I was able to do this by selected the appropriate groups of
dates and using "Group" to group by month, then Group to group by year.

First question, that has to be so common it seems like there must be an
easier way.

Next, a month goes by and I refresh the sales data (using Xml/Refresh Data).
I save. Now I go to my pivot table and refresh data. As I would expect there
are new dates in the series that are not part of any existing group.
Typically there are additional dates that should have fallen in already
grouped months but the data wasn't present at the time of the last run.
Getting these dates into the appropriate group is a nightmare of ungrouping,
dragging, guessing why a row won't move where I want and then finally
grouping again.

Second question, isn't there a way to update the underlying list so that
data will get into the pivot table in the correct grouping?

I'd really appreciate some help here, thanks!

Hi Philip.

Do not select a range of dates and then group.

Start with ungrouped dates, then right-click the date field header and
select Group from the drop-down menu...

Be sure that the date field does not contain blanks, or you will not
be able to group the whole field by date.

Ed Ferrero
http://www.edferrero.com

> This has got to be a common problem, but I am not finding the solution. I
> import sales data via an xml list. I created the pivot table on the series
> of dates the sales happened. It naturally groups the data with summary
> columns for the number of sales on each date, just what I would expect.
>
> Now I want to that those figures additionally grouped by month, then year.
> Though tedious, I was able to do this by selected the appropriate groups
> of dates and using "Group" to group by month, then Group to group by year.
>
> First question, that has to be so common it seems like there must be an
> easier way.
>
> Next, a month goes by and I refresh the sales data (using Xml/Refresh
> Data). I save. Now I go to my pivot table and refresh data. As I would
> expect there are new dates in the series that are not part of any existing
> group. Typically there are additional dates that should have fallen in
> already grouped months but the data wasn't present at the time of the last
> run. Getting these dates into the appropriate group is a nightmare of
> ungrouping, dragging, guessing why a row won't move where I want and then
> finally grouping again.
>
> Second question, isn't there a way to update the underlying list so that
> data will get into the pivot table in the correct grouping?
>
> I'd really appreciate some help here, thanks!
>

Wont that just group by activity on the same date? The intial try just gave
me an error saying it couldn't group by that field.
I'll experiment though.

Perhaps a calculated fields would get me the month and year and I could
group by that.

"Ed Ferrero" <[email protected]> wrote in message
news:%[email protected]...
> Hi Philip.
>
> Do not select a range of dates and then group.
>
> Start with ungrouped dates, then right-click the date field header and
> select Group from the drop-down menu...
>
> Be sure that the date field does not contain blanks, or you will not
> be able to group the whole field by date.
>
> Ed Ferrero
> http://www.edferrero.com
>
>> This has got to be a common problem, but I am not finding the solution. I
>> import sales data via an xml list. I created the pivot table on the
>> series of dates the sales happened. It naturally groups the data with
>> summary columns for the number of sales on each date, just what I would
>> expect.
>>
>> Now I want to that those figures additionally grouped by month, then
>> year. Though tedious, I was able to do this by selected the appropriate
>> groups of dates and using "Group" to group by month, then Group to group
>> by year.
>>
>> First question, that has to be so common it seems like there must be an
>> easier way.
>>
>> Next, a month goes by and I refresh the sales data (using Xml/Refresh
>> Data). I save. Now I go to my pivot table and refresh data. As I would
>> expect there are new dates in the series that are not part of any
>> existing group. Typically there are additional dates that should have
>> fallen in already grouped months but the data wasn't present at the time
>> of the last run. Getting these dates into the appropriate group is a
>> nightmare of ungrouping, dragging, guessing why a row won't move where I
>> want and then finally grouping again.
>>
>> Second question, isn't there a way to update the underlying list so that
>> data will get into the pivot table in the correct grouping?
>>
>> I'd really appreciate some help here, thanks!
>>
>
>

A second follow up. According to the pivot table there is a blank date in
the list. There are no blank dates according to the xml list. I thought it
might be the total row, nope. I added another field to help me figure out
what row it was unhappy about, the name field. I filter the pivot table by
blank date: no rows found. sigh...

Any ideas? I cannot group until I get this solved "cannot group by this
selection"

"Ed Ferrero" <[email protected]> wrote in message
news:%[email protected]...
> Hi Philip.
>
> Do not select a range of dates and then group.
>
> Start with ungrouped dates, then right-click the date field header and
> select Group from the drop-down menu...
>
> Be sure that the date field does not contain blanks, or you will not
> be able to group the whole field by date.
>
> Ed Ferrero
> http://www.edferrero.com
>
>> This has got to be a common problem, but I am not finding the solution. I
>> import sales data via an xml list. I created the pivot table on the
>> series of dates the sales happened. It naturally groups the data with
>> summary columns for the number of sales on each date, just what I would
>> expect.
>>
>> Now I want to that those figures additionally grouped by month, then
>> year. Though tedious, I was able to do this by selected the appropriate
>> groups of dates and using "Group" to group by month, then Group to group
>> by year.
>>
>> First question, that has to be so common it seems like there must be an
>> easier way.
>>
>> Next, a month goes by and I refresh the sales data (using Xml/Refresh
>> Data). I save. Now I go to my pivot table and refresh data. As I would
>> expect there are new dates in the series that are not part of any
>> existing group. Typically there are additional dates that should have
>> fallen in already grouped months but the data wasn't present at the time
>> of the last run. Getting these dates into the appropriate group is a
>> nightmare of ungrouping, dragging, guessing why a row won't move where I
>> want and then finally grouping again.
>>
>> Second question, isn't there a way to update the underlying list so that
>> data will get into the pivot table in the correct grouping?
>>
>> I'd really appreciate some help here, thanks!
>>
>
>

Hi Philip,

I just imported an xml list, created a pivot table, and grouped by date with
no problems. I suggest you check the data in your xml list. Maybe check that
you have dates in column with a formula like
=IF(ISERR(DAY(A2)),"PROBLEM",IF(ISTEXT(A2),"PROBLEM",""))

Ed Ferrero
http://www.edferrero.com


>A second follow up. According to the pivot table there is a blank date in
>the list. There are no blank dates according to the xml list. I thought it
>might be the total row, nope. I added another field to help me figure out
>what row it was unhappy about, the name field. I filter the pivot table by
>blank date: no rows found. sigh...
>
> Any ideas? I cannot group until I get this solved "cannot group by this
> selection"