12 Month Rolling Average in Pivot Table

[briesen]

I have a pivot table that is grouped by a category (Department), another category (Employee), the year, and the month. The data is from January of 2007 up to April 2008. Right now, I have it so it totals each column for the month and then for the employee. If I try to average it, it averages for all the data since January 2007.

Instead of summing by the employee totals, I want to do a 12 month rolling average for the last 12 months while still displaying the data from before that.

Can anyone help me figure this out without changing the database?

 

[SkipVought]

Hi,


I'd try to use the SUMPRODUCT function to return the aggregation. Something like...
[tt]
=SUMPRODUCT((YourDeptSourceRange=DeptValRef)*
(YourEmplSourceRange=EmplValRef)*
(YourDateSourceRange>=TODAY()-365)*
(YourDateSourceRange<TODAY())*
(YourAmountSourceRange))
[/tt]



Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[briesen]

The data I'm grabbing is from an external source, not Excel. I'm basically trying to grab a rolling average for the past twelve months even though I want my table to display the last two years (and put zeros if nothing for that month so it averages correctly).

It doesn't seem like this is possible with manipulating the actual source data because I can't do manual formulas, just calculated field ones unless I'm missing something.

 

[SkipVought]

So what's the SQL for the source data as it currently stands?

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}