Essbase @MOVSUMX

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

Unlike @MOVSUM, @MOVSUMX allows you to specify the values assigned to
trailing members. For example, if you specify three members of the Time dimension
in the Sample Basic database, @MOVSUMX at Mar is the sum of the values for
Jan, Feb, and Mar; @MOVSUMX at Apr is the sum of the values for Feb, Mar,
and Apr. However, Jan and Feb have no @MOVSUMX value, and are called trailing members.

Syntax

@MOVSUMX (COPYFORWARD | TRAILMISSING | TRAILSUM, mbrName [,n[,Xrangelist]] )
Parameter Description
COPYFORWARD Copies the input value into the trailing members. This behavior is the
same as the @MOVSUM function.
TRAILMISSING Sets the value of the trailing members to #MISSING.
TRAILSUM Sums the trailing values.
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 maximum. 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 @MOVSUMX 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, @MOVSUMX skips #MISSING values.
    For example, if one value out of three is #MISSING, Essbase adds the remaining two values.
  • 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 that you are using.
  • When you use @MOVSUMX in a calculation script, use it within
    a FIX statement. Although FIX is not required, using it may improve calculation
    performance.
  • When you use @MOVSUMX 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 examples are based on the Sample Basic database. Assume
that the Measures dimension contains an additional member, “Last 3 Months
of Sales,” and that the original Sales values are as shown.

Last 3 Months of Sales = @MOVSUMX (COPYFORWARD,Sales,3,Jan:Aug);

or:

Last 3 Months of Sales = @MOVSUMX (TRAILMISSING,Sales,3,Jan:Aug);

or:

Last 3 Months of Sales = @MOVSUMX (TRAILSUM,Sales,3,Jan:Aug);

These examples produce the following reports:

Sales

===========
Jan     100
Feb     150
Mar     200
Apr     250
May     300
Jun     350
Jul     400
Aug     450
Last 3 Months of Sales
COPYFORWARD
======================
          100
          150
          450
          600
          750
          900
         1050
         1200
Last 3 Months of Sales
TRAILMISSING
======================
          #MISSING
          #MISSING
          450
          600
          750
          900
         1050
         1200
Last 3 Months of Sales
TRAILSUM
======================
          100
          250
          450
          600
          750
          900
         1050
         1200

See Also

  • @MOVAVG
  • @MOVMAX
  • @MOVMED
  • @MOVMIN
  • @MOVSUM
  • @TREND