Rolling 12 in a Pivot table

[thefox149]

I am putting together a pivot table and rather then just summarizing the data for each month I would like each months results to be a rolling 12 eg

RTM Amount
Jun 05 1250 this amount is the sum of(Jul 04 to Jun 05)
Jul 05 1100 this amount is the sum of(Aug 04 to Jul 05)
Aug 05 1200 this amount is the sum of(Sep 04 to Aug 05)
etc

My cat's name is sprinkles
-Ralph Wigam

 

[KenWright]

Only way I can think of is by adding another field to your source data that does just that, eg assuming your Amount data was in col C and your date value was in column B, then in a column at the end of your data, call the header Amt2 or something and use the following:-

=SUMPRODUCT(--($B$2:$B$2000>DATE(YEAR(B2)-1,MONTH(B2),DAY(B2))),--($B$2:$B$2000<=B2),$C$2:$C$2000)

Then change that field to MAX instead of SUM using Field Settings.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[thefox149]

thanks ken funnily enough i did solve it with sumproduct ...

My cat's name is sprinkles
-Ralph Wigam

 

[wec43wec]

What does the "--" do in the formula below?

=SUMPRODUCT(--($B$2:$B$2000>DATE(YEAR(B2)-1,MONTH(B2),DAY(B2))),--($B$2:$B$2000<=B2),$C$2:$C$2000)

 

[KenWright]

See here:-

http://www.mcgimpsey.com/excel/formulae/doubleneg.html

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[thefox149]

this shaved a minute of a 5 minute complicated sumproduct I had....not bad have a star

My cat's name is sprinkles
-Ralph Wigam