Pivot table grouping

[Philip Nelson]

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!

 

[Ed Ferrero]

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

 

[Philip Nelson]

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.

 

[Philip Nelson]

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]

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