CUMSUM

The CUMSUM function computes cumulative totals over time or over another dimension. When the data being totaled is one-dimensional, CUMSUM produces a single series of totals, one for all values of the dimension. When the data has dimensions other than the one being totaled over, CUMSUM produces a separate series of totals for each combination of values in the status of the other dimensions.

Return Value

DECIMAL

Syntax

CUMSUM(cum-expression [STATUS] total-dim [reset-dim] [INSTAT])

Arguments

cum-expression

A numeric variable or calculation whose values you want to total, for example UNITS.

STATUS

When cum-expression is multidimensional, CUMSUM creates a temporary variable to use while processing the function. When you specify the STATUS keyword, CUMSUM uses the current status instead of the default status of the dimensions for calculating the size of this temporary variable. When the dimensions of the expression are limited to a few values and are physically fragmented, you can improve the performance of CUMSUM by specifying STATUS.

When you use CUMSUM with the STATUS keyword in an expression that requires going outside of status for results (for example, with the LEAD or LAG functions or with a qualified data reference), the results outside of status will be returned as NA.

Note: When you specify the STATUS keyword when the data being totaled is one-dimensional, an error results

total-dim

The dimension of cum-expression over which you want to total.

reset-dim

Specifies that the cumulative totals in a series should start over with each new reset dimension value, for example at the start of each new year. The reset dimension can be any of the following:

• Any dimension related to total-dim through an explicitly defined relation.

• Any dimension with a type of DAY, WEEK, MONTH, QUARTER, or YEAR, when total-dim also has a type of DAY, WEEK, MONTH, QUARTER, or YEAR. CUMSUM uses the implicit relation between the two dimensions, so they do not need to be related through an explicit relation. See "Overriding an Implicit Relation".

• A relation dimensioned by total-dim. CUMSUM uses the related dimension as the reset dimension. This enables you to choose which relation is used when there is more than one.

INSTAT

Specifies that CUMSUM uses only the values of total-dim that are currently in status. When you do not specify INSTAT, CUMSUM produces a total for all the values of total-dim, independent of its current status. See "INSTAT Ignores Current Status By Default".

Notes

Overriding an Implicit Relation

When you specify dimensions with a type of DAY, WEEK, MONTH, QUARTER, or YEAR for both the total-dim argument and the reset-dim argument, CUMSUM uses the implicit relation between the two dimensions even when an explicit relation exists. However, you can override the default and use the explicit relation by specifying the name of the relation for the reset-dim argument.

INSTAT Ignores Current Status By Default

Unless you specify the INSTAT keyword, CUMSUM ignores the current status in calculating totals. Suppose MONTH is the dimension being totaled over (and INSTAT has not been specified). The CUMSUM total for a given month uses the values for all preceding months, even when some are not in the status. When a reset dimension is specified, the total for a given month uses the values for all preceding months that correspond to the same value of the reset dimension (for example, all preceding months in the same year). To calculate year-to-date totals, specify YEAR as the reset dimension.

Examples

LIMIT district TO 'Atlanta'
LIMIT product TO 'Tents' 'Canoes'
LIMIT month TO 'Jan96' TO 'Jun96'
REPORT DOWN month units CUMSUM(units, month year) -
   CUMSUM(units, month)

DISTRICT: ATLANTA
         ------------------------PRODUCT------------------------ 
         ---------TENTS------------- ---------CANOES------------ 
               CUMSUM(UNI                   CUMSUM(UNI
                TS, MONTH CUMSUM(UNI         TS, MONTH CUMSUM(UNI
MONTH    UNITS    YEAR)   TS, MONTH)  UNITS    YEAR)   TS, MONTH)
-----  -------- --------- ---------- ------- --------- ----------
Jan96      279       279      5,999      281       281      5,162
Feb96      305       584      6,304      309       590      5,471
Mar96      356       940      6,660      386       976      5,857
Apr96      537     1,477      7,197      546     1,522      6,403
May96      646     2,123      7,843      525     2,047      6,928
Jun96      760     2,883      8,603      608     2,655      7,536

The totals for CUMSUM(UNITS, MONTH) include values for all months beginning with the first month, JAN95. The totals for CUMSUM(UNITS, MONTH YEAR) include only the values starting with JAN96.

LIMIT district TO 'Atlanta'
LIMIT product TO 'Tents' 'Canoes'
limit month TO year 'Yr96'
REPORT DOWN month units CUMSUM(units, month quarter)

DISTRICT: ATLANTA
             ------------------PRODUCT------------------
             --------TENTS-------- -------CANOES--------
                          CUMSUM(UNI          CUMSUM(UNI
                           TS, MONTH           TS, MONTH
MONTH           UNITS     QUARTER)    UNITS     QUARTER)
------------ ---------- ---------- ---------- ----------
Jan96             279        279        281        281
Feb96             305        584        309        590
Mar96             356        940        386        976
Apr96             537        537        546        546
May96             646      1,183        525      1,071
Jun96             760      1,943        608      1,679
Jul96             852        852        626        626
Aug96             730      1,582        528      1,154
Sep96             620      2,202        520      1,674
Oct96             554        554        339        339
Nov96             380        934        309        648
Dec96             284      1,218        288        936