Essbase @MOVSUM

Applies a moving sum to the specified number of values in an input data
set. @MOVSUM modifies a data set for smoothing purposes.

Syntax

@MOVSUM (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 sum. 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

  • For example, if you specify 3 members of the Time dimension
    in the Sample Basic database, @MOVSUM at Mar is the sum of the values for
    Jan, Feb, and Mar; @MOVSUM at Apr is the sum of the values for Feb, Mar, and
    Apr. However, Jan and Feb have no @MOVSUM value, and are called trailing members.
    Trailing members are copies of the input values. If you wish to assign different
    values to trailing members, use @MOVSUMX instead.
  • The @MOVSUM function calculates a trailing, rather than a
    centered, sum. This example illustrates the difference:

       Trailing Sum           Centered Sum
        1   2   3              1   2   3
                6                  6
  • While calculating the moving sum, @MOVSUM skips #MISSING values.
    For example, if one value out of three is #MISSING, Essbase adds the remaining two values.
  • 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 that
    you are using.
  • When you use @MOVSUM in a calculation script, use it within
    a FIX statement. Although FIX is not required, using it may improve calculation
    performance.
  • When you use @MOVSUM 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 Sum.

"Mov Sum" = @MOVSUM(Sales,3,Jan:Jun);

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

This example produces the following report:

          Colas   New York   Actual
              Sales     Mov Sum
              =====     =======
Jan            678        678
Feb            645        645
Mar            675       1998
Apr            712       2032
May            756       2143
Jun            890       2358

See Also

  • @MOVAVG
  • @MOVMAX
  • @MOVMED
  • @MOVMIN
  • @MOVSUMX
  • @TREND