| Oracle® OLAP DML Reference 10g Release 1 (10.1) Part Number B10339-02 |
|
|
View PDF |
| Oracle® OLAP DML Reference 10g Release 1 (10.1) Part Number B10339-02 |
|
|
View PDF |
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.
By default, CUMSUM ignores the current status of the dimension over which it is calculating totals. You can override this behavior by specifying the INSTAT keyword.
Return Value
DECIMAL
Syntax
CUMSUM(cum-expression [STATUS] total-dim [reset-dim] [INSTAT])
Arguments
A numeric variable or calculation whose values you want to total, for example UNITS.
May be specified to improve the performance of CUMSUM when cum-expression has more than one dimension. When you specify the STATUS keyword when the data being totaled is one-dimensional, an error results. For more information, see "Using the STATUS Keyword".
The dimension of cum-expression over which you want to total.
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.
May be specified to cause CUMSUM to use 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 "Current Status Ignored".
Notes
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.
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.
CUMSUM is affected by the NASKIP option. When NASKIP is set to YES (the default), CUMSUM ignores NA values and returns a cumulative total using the available values. When NASKIP is set to NO, CUMSUM returns NA when any data value has a value of NA. When all the values are NA, CUMSUM returns NA for either setting of NASKIP.
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.
Examples
Example 9-29 Multiple CUMSUM Calculations
This example shows cumulative units totals for tents and canoes in the Atlanta district for the first six months of 1996. The report shows the units figures themselves, year-to-date totals calculated using year as the reset dimension, and totals calculated with no reset dimension using all preceding months. Assume that you issue the following statements.
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)
The following report is displayed.
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.
Example 9-30 Resetting for a Quarter
This example shows cumulative totals for the same products and district, for the entire year 1996. Because quarter is specified as the reset dimension, totals start accumulating at the beginning of each quarter. The cumulative totals for Jan96, Apr96, Jul96, and Oct96 are the same as the units figures for those months. Assume that you issue the following statements.
LIMIT district TO 'Atlanta' LIMIT product TO 'Tents' 'Canoes' limit month TO year 'Yr96' REPORT DOWN month units CUMSUM(units, month quarter)
A report displays.
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
|
 Copyright © 2003 Oracle Corporation All Rights Reserved. |
|