Essbase @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.

See Also

  • @MOVMAX
  • @MOVMED
  • @MOVMIN
  • @MOVSUM
  • @MOVSUMX
  • @TREND