Skip Headers
Oracle® OLAP DML Reference
10g Release 2 (10.2)

Part Number B14346-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

NASKIP

The NASKIP option controls whether NA values are considered as input to aggregation functions.

See also:

$NATRIGGER, NASKIP2 that controls how NA values are treated with the + (plus) and - (minus) operators, and NASPELL

Data type

BOOLEAN

Syntax

NASKIP = NO|YES

Arguments

NO

(Default) NA values are considered by aggregation functions. When any of the values being considered are NA, the function returns NA for that value.

YES

NA values are ignored by aggregation functions. Only expressions with actual values are used in calculations.

Notes

Statements Affected by NASKIP

The following OLAP DML statements are affected by NASKIP.

AGGREGATE command
AGGREGATE function
ANY
AVERAGE
COUNT
CUMSUM
DEPRDECL
DEPRDECLSW
DEPRSL
DEPRSOYD
EVERY
FINTSCHED
FPMTSCHED
IRR
LARGEST
MEDIAN
MOVINGAVERAGE
MOVINGMAX
MOVINGMIN
MOVINGTOTAL
NONE
NPV
SMALLEST
STDDEV
TCONVERT
TOTAL
VINTSCHED
VPMTSCHED

Other statements are not affected by the setting of NASKIP, they always ignore NA values.

Examples

Example 20-14 The Effect of NASKIP on the TOTAL Function

In the demo workspace, the 1997 values for sales are NA. The TOTAL function returns different results depending on the setting of NASKIP.

The statements

ALLSTAT
NASKIP = YES
SHOW TOTAL(sales)

produce the following result.

63,181,743.50

In contrast, the OLAP DML statements

NASKIP = NO
SHOW TOTAL(sales)

produce the following result.

NA

Example 20-15 The Effect of NASKIP on the MOVINGMIN Function

This example aggregates values for three months: the current month and the two months before it. The first report of SALES shows the NA values for months in 1997. When NASKIP is YES, the MOVINGMIN function returns NA only for March 1997 because all the values considered for that month were NA. When NASKIP is NO, the third statement (REPORT DOWN month sales) shows NA values for January through March 1997, because at least one value considered by MOVINGMIN for those months was NA.

LIMIT district TO 'Seattle'
LIMIT month TO 'Jul96' TO 'Mar97'
REPORT DOWN month sales

The preceding statements produce the following report of SALES data.

DISTRICT: SEATTLE
      ------------------------SALES-------------------------
      -----------------------PRODUCT------------------------
MONTH   Tents      Canoes    Racquets  Sportswear  Footwear
----- ---------- ---------- ---------- ----------  ---------
Jul96 123,700.17 157,274.03  60,198.52  78,305.97  78,019.87
Aug96 120,650.72 128,660.89  45,046.71  66,853.26  83,347.55
Sep96  97,188.43 122,702.13  42,257.14  63,777.36  99,464.05
Oct96  91,578.77  79,925.93  39,729.25  55,021.85  83,537.58
Nov96  56,044.34  77,357.10  39,024.93  44,004.12  65,216.94
Dec96  41,576.26  67,609.36  36,156.10  40,575.34  62,113.72
Jan97         NA         NA         NA         NA         NA
Feb97         NA         NA         NA         NA         NA
Mar97         NA         NA         NA         NA         NA

The statements

NASKIP = YES
REPORT DOWN month MOVINGMIN(sales -2, 0, 1, month)

produce the following report, which shows NA values for March 1997.

DISTRICT: SEATTLE
      -----------MOVINGMIN(SALES -2, 0, 1, MONTH)-----------
      ---------------------PRODUCT--------------------------
MONTH   Tents      Canoes    Racquets  Sportswear  Footwear
----- ---------- ---------- ---------- ----------  ---------
Jul96 108,663.59 125,823.37 57,666.37  57,713.27   73,085.88
Aug96 119,066.18 128,660.89 45,046.71  60,322.88   78,019.87
Sep96  97,188.43 122,702.13 42,257.14  63,777.36   78,019.87
Oct96  91,578.77  79,925.93 39,729.25  55,021.85   83,347.55
Nov96  56,044.34  77,357.10 39,024.93  44,004.12   65,216.94
Dec96  41,576.26  67,609.36 36,156.10  40,575.34   62,113.72
Jan97  41,576.26  67,609.36 36,156.10  40,575.34   62,113.72
Feb97  41,576.26  67,609.36 36,156.10  40,575.34   62,113.72
Mar97         NA         NA         NA         NA         NA

The statements

NASKIP = NO
REPORT DOWN month MOVINGMIN(sales -2, 0, 1, month)

produce the following report, which shows NA values for January through March 1997.

DISTRICT: SEATTLE
      ----------MOVINGMIN(SALES -2, 0, 1, MONTH)-------------
      ------------------------PRODUCT------------------------
MONTH   Tents      Canoes    Racquets  Sportswear  Footwear
----- ---------- ---------- ---------- ----------  ----------
Jul96 108,663.59 125,823.37  57,666.37  57,713.27   73,085.88
Aug96 119,066.18 128,660.89  45,046.71  60,322.88   78,019.87
Sep96  97,188.43 122,702.13  42,257.14  63,777.36   78,019.87
Oct96  91,578.77  79,925.93  39,729.25  55,021.85   83,347.55
Nov96  56,044.34  77,357.10  39,024.93  44,004.12   65,216.94
Dec96  41,576.26  67,609.36  36,156.10  40,575.34   62,113.72
Jan97         NA         NA         NA         NA          NA
Feb97         NA         NA         NA         NA          NA
Mar97         NA         NA         NA         NA          NA