Skip Headers
Oracle® OLAP DML Reference
11g Release 1 (11.1)

B28126-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

LAGPCT

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.

See also:

"Using LAGPCT"

Return Value

DECIMAL or NA when you try to lag prior to the first period of a dimension of a time dimension.

Syntax

LAGPCT(variablen, [dimension], [STATUS|NOSTATUS|limit-clause] )

Arguments

variable

A variable or expression that is dimensioned by dimension.

n

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. Typically, 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.

dimension

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.

STATUS

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.

NOSTATUS

(Default) Specifies that LAGPCT should use the default status (that is, a list all the dimension values in their original order) when computing the lag.

limit-clause

Specifies that LAGPCT should use the default status limited by limit-clause when computing the lag.

The syntax of limit-clause is the same syntax as any of the limit-clause arguments in the various forms of the LIMIT command (that is, the syntax of the LIMIT command after the limit-type argument such as "TO"). For the syntax of these arguments, see LIMIT (using values) command, LIMIT using LEVELREL command, LIMIT command (using parent relation), LIMIT (using related dimension) command, LIMIT NOCONVERT command, and LIMIT command (using POSLIST).

To specify that LAG should use the current status limited by limit-clause when computing the lag, specify a LIMIT function for limit-clause.

Notes

Formula Used by LAGPCT

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

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.

Examples

Example 8-3 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 by using the LAG function to show 199 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.

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%