Oracle® OLAP DML Reference 10g Release 1 (10.1) Part Number B1033902 


View PDF 
The LAGPCT function returns the percentage difference between the value of a dimensioned variable or expression at a specified offset of a dimension prior to the current value of that dimension and the current value of the dimensioned variable or expression.
Return Value
DECIMAL or NA
when you try to lag prior to the first period of a dimension of a time dimension.
Syntax
LAGPCT(variable, n, [dimension], [STATUSNOSTATUSlimitclause] )
Arguments
A variable or expression that is dimensioned by dimension.
The offset (that is, the number of dimension values) to lag. LAGPCT uses this value to determine the number of values that LAGPCT should go back in dimension to retrieve the value of variable. (See "Negative n Value".) To count the values, LAGPCT uses the default status, unless you use the STATUS keyword or the limitclause argument to specify a different dimension status.
The dimension along which the lag occurs. While this can be any dimension, it is typically a hierarchical time dimension of type TEXT that is limited to a single level (for example, the month or year level) or a dimension with a type of DAY, WEEK, MONTH, QUARTER or YEAR.
When variable has a dimension with a type of DAY, WEEK, MONTH, QUARTER, or YEAR and you want LAGPCT to use that dimension, you can omit the dimension argument.
Specifies that LAGPCT should use the current status list (that is, only the dimension values currently in status in their current status order) when computing the lag.
Specifies that LAGPCT should use the default status (that is, a list all the dimension values in their original order) when computing the lag.
Specifies that LAGPCT should use the default status limited by limitclause when computing the lag. You can use any valid LIMIT clause (see the entry for the LIMIT command for further information). To specify that LAGPCT should use the current status limited by limitclause when computing the lag, specify a LIMIT function for limitclause.
Notes
To obtain its results, LAGPCT uses the following formula.
(currentvalue  previousvalue) / previousvalue
When the previous value of the time series used by LAGPCT is zero, the result LAGPCT returns is determined by the DIVIDEBYZERO option. When DIVIDEBYZERO is set to NO
, an error occurs. When DIVIDEBYZERO is set to YES
, LAGPCT returns NA
.
LAGPCT returns a decimal value that corresponds to a percent difference. To represent this value as percentage points, you can multiply it by 100
. See "Using LAGPCT".
Normally, n is a positive integer that indicates the number of time periods (or dimension values) before the current one. When you specify a negative value for n, it indicates the number of time periods after the current one. In this case, LAGPCT compares the current value of the time series with a subsequent value.
LAGPCT ignores NASKIP2. NASKIP2 does not control how NA
values are treated in OLAP DML functions. It only controls arithmetic operations involving the +
(plus) and 
(minus) operators that are executed at the command line and in programs, models, and formulas.
Examples
Example 1532 Using LAGPCT
Suppose you have a variable called sales
that is dimensioned by a hierarchical dimension named time
, and dimensions called district
and products
. Assume also that there is a dimension named timelevels
that contains the names of the levels of the time
dimension (that is, Month
and Year
) and a relation named timelevelrel
that is dimensioned by time
and that has values from timelevels
(that is, the related dimension of timelevelrel
is timelevels
).
You can compare racquet sales in Dallas for 2000 with sales for 1999. You can use the LAG function to show values from 1999 in the same report with the 2000 values. You can use the LAGPCT function to calculate the percentage difference between the two. You can multiply the value LAGPCT returns by 100 and include a percent sign to display the difference as percentage points.
The following statements
ALLSTAT LIMIT product TO 'Racquets' LIMIT district TO 'Dallas' LIMIT TIME TO '2000' REPORT DOWN time sales HEADING 'Last Year'  LAG(sales, 1, time, time LEVELREL timelevelrel) HEADING 'LAGPCT (Decimal Format)'  LAGPCT(sales, 1, time LEVELREL timelevelrel)  HEADING 'LAGPCT (Percent Format)' rset '%'  LAGPCT(sales, 1, time LEVELREL timelevelrel) * 100
produce this report.
DISTRICT: Dallas PRODUCT racquets LAGPCT LAGPCT (Decimal (Percent TIME SALES Last Year Format) Format)      2000 93,000,003 89,000,891 0.04 4.49%