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!