Essbase @MOVMED

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

Syntax

@MOVMED (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
that are used to calculate the moving median. 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

  • While calculating the moving median, the @MOVMED function
    skips #MISSING values. For example, if one value out of four
    is #MISSING, @MOVMED calculates the median of the remaining three
    values.
  • The @MOVMED function calculates a trailing, rather than a
    centered, median. For example:

      Trailing Median        Centered Median
        1   2   3              1   2   3
                2                  2
  • If the group of values being used to calculate the median
    contains an even number of values, the @MOVMED function averages the two numbers
    in the middle.
  • If you use an Essbase 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 @MOVMED in a calculation script, use it within
    a FIX statement. Although FIX is not required, using it may improve calculation
    performance.
  • When you use @MOVMED 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 Med.

"Mov Med" = @MOVMED(Sales,3,Jan:Jun);

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

This example produces the following report:

            Colas      New York     Actual
                Sales        Mov Med
                =====        =======
Jan              678           678
Feb              645           645
Mar              675           675
Apr              712           675
May              756           712
Jun              890           756

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

See Also

  • @MOVAVG
  • @MOVMAX
  • @MOVMIN
  • @MOVSUM
  • @MOVSUMX
  • @TREND