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.
@MOVAVG (mbrName [, n [, XrangeList]])
|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.
- 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
- 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
- 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.
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
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.