@MOVAVG

Applies a moving n-term average (mean) to an input data set. Each term in the set is replaced by a trailing mean of n terms, and the first terms (the n-1 terms) are copies of the input data. @MOVAVG modifies a data set for smoothing purposes.

Syntax

@MOVAVG (mbrName [, n [, XrangeList]])

Parameter	Description
mbrName	Any valid single member name or member combination, or a function that returns a single member or member combination.
n	Optional. A positive integer value that represents the number of values to average. The default is 3.
XrangeList	Optional. A valid member name, a comma-delimited list of member names, cross dimension members, or a member set function or range function (including @XRANGE that returns a list of members from the same dimension. If XrangeList is not specified, Essbase uses the level 0 members from the dimension tagged as time.

Notes

The @MOVAVG function calculates a trailing, rather than a centered, average. For example:

  Trailing Average       Centered Average 
     1   2   3              1   2   3
             2                  2

While calculating the moving average, the @MOVAVG function skips #MISSING values and decreases the denominator accordingly. For example, if one value out of three is #MISSING, Essbase adds the remaining two values and divides the sum by two.
If you use a member set function to generate a member list for the XrangeList parameter (for example, @SIBLINGS), to ensure correct results, consider the order in which Essbase sorts the generated member list. For more information, see the Oracle Essbase Technical Reference topic for the member set function you are using.
When you use @MOVAVG in a calculation script, use it within a FIX statement. Although FIX is not required, using it may improve calculation performance.
For periods where the width is undefined, the value is the same as for the source member. For example, you can't compute the moving average over the last three months for Jan and Feb because it doesn't exist. When this happens, Essbase simply copies the value for Jan and Feb for the moving average.
When you use @MOVAVG across a large range in a sparse dimension, you may need to increase the size of the calculator cache. For more information on the calculator cache, see the Oracle Essbase Database Administrator's Guide.

Example

The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Mov Avg.

"Mov Avg" = @MOVAVG(Sales,3,Jan:Jun);

In this example, the @MOVAVG function smooths sales data for the first six months of the year (Jan through Jun). The results of @MOVAVG can be used with the @TREND function to forecast average sales data for a holiday season (for example, October - December).

This example produces the following report:

          Colas   New York   Actual              
              Sales     Mov Avg 
              =====     =======   
Jan            678        678   
Feb            645        645   
Mar            675        666   
Apr            712        677.3 
May            756        714.3 
Jun            890        786

In this example, Essbase averages three values at a time for the moving average. The first two values (Jan,Feb) for Mov Avg and the first two values for Sales are the same. The value for Mar represents the trailing average of Jan, Feb, and Mar. The value for Apr represents the trailing average of Feb, Mar, and Apr. The remaining values represent the trailing average for each group of three values.