pivot table date problem

[brianfm]

Hi,

I want create a simple pivot table that sum a number amount for each customer.
Each row also have a date field.
The part I am having trouble with is I only want rows whose date is within the last year to be included. I have tried grouping the data by year and in that dialog I can choose a minimum/maximum date. However, its not what I want as the data is being being summed per year, I want all data summed over within the last year from the current date.
So, for the table below, the summed amount should be 11 as the first row is not in the last year,

id balance date
1 6 01/03/09
1 8 03/09/09
1 3 06/02/10

thanks,

Brian

[teylyn]

group the data by year, then use the filter to show only the year you want to see.

The Min/Max of the grouping is not meant to filter the data. You filter with the drop-down box of the field.

[brianfm]

Hi,

thanks for your reply.
Your suggestion does not work, because I do not want to group the data by year, I want to only include data from the current date to 365 days ago, that is why the first row in the result set from the data example above,

thanks,

Brian

[teylyn]

OK,

group the date field thus:

Starting at: 1-Aug-2009
Ending at: 31-Jul-2010

Select Month in the "By" list.

Now filter by date. You will see tick boxes for the months, but you will also see tickboxes for "<01/08/2009" and ">31/07/2010". Unselect the latter two from the filter and you will see only data for the last year.

see attached.

[MarvinP]

Hey Brian,
I couldn't do the date filter using 2003 Excel but after saving it in Excel 2010 (2007 too?) format I was able to filter dates after a year ago. See attached. Perhaps this is a good reason to update to a newer Excel version?

[teylyn]

Marvin, the date filter I applied was done in 2003. I try to use the version the OP states, otherwise all the wonderful stuff you can do in 2010 may get lost. There's simply no point applying features that Office 2003 can not cope with.

[brianfm]

Hi,

thanks again for your reply.
Your proposed solution does indeed show only data for last year, however the data is also grouped by month, so I am getting monthly totals in the resultant pivot table. I would like to see the total for the whole year,

thanks,

Brian

[teylyn]

can you please provide a data sample and manually mock up what you would like to see as the result?

[teylyn]

Use the same grouping, unselect "months", select "days" instead and in "number of days" enter 365.

Now, if you cannot be bothered to provide a sample of what you want, I can not be bothered to answer your question any further.

cheers