3 Replies Latest reply on Jan 16, 2013 10:37 AM by Shawn Wallwork

# Compute a Moving average on a calculated field

Hi,

I am looking to compute a moving average on a calculated field but do not manage to do so. Would you know if it is possible to achieve that ?

Alexis

• ###### 1. Re: Compute a Moving average on a calculated field

Alexis, generally speaking the answer is "yes". But we'll need to know what your calculated field is, and how it's being used. Please post a sample packaged workbook with dummy or sample data. Thanks,

--Shawn

• ###### 2. Re: Compute a Moving average on a calculated field

Hi Shawn,

1) I have some data from which I create a calculated field in the second column, in order to see the variation period after period (Quick Table Calculation, and Percent Difference)

2) What I would like to do is to use the calculated field previously created in order to have a third column with a 4 Quarter Moving Average of my variation.

I manage to do a moving average of a non calculated field (e.g. Customers column), but do not succeed to do so with a calculated field as explained.

Thanks again,

Alexis

• ###### 3. Re: Compute a Moving average on a calculated field

Alexis, all you need to do is wrap the WINDOW_AVG() calculation around the percent difference calc:

WINDOW_AVG((ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1)) / ABS(LOOKUP(ZN(SUM([Sales])), -1)),-4,0)

The -4 sets how many previous rows you want to go back and average. In the attached I set it to show a blank if there weren't enough previous row.

--Shawn