Go to start of banner

# OLAP: Formula Calculation and Special Values(Null, Error, NOP,...)

## Introduction

This wiki page explains the basic rules that the OLAP Engine applies when calculations contain special values like Null or DIV0. The following SAP note and link to the SAP Online documentation provide a detailed discussion of this topic

List of special values:

• NULL represents the value null (no value)
• DIV0 is the result of a division by 0;
• ERROR displays an error;
• NOP displays that no aggregation is possible - see Key Figures with Aggregation NOP/NO2

## Null Values

The behavior of null values in formulas is not defined clearly in a mathematical sense. As explained in note 794006, the most important rules applied by the Analytic Engine are

• O1(N) = N
• e.g. formula = NODIM(Null) = Null
• O2(N,N) = N
• e.g. multiplication of two (retsricted) key figures: formula = Null * Null = Null
• O2(N,W) = O2(0,W): with the exception of combinations like N * W, N / W, N %A W which return N as result (see note 794006 for details)
• e.g. multiplication of a key figure by a constant: formula = Null * c = Null
• e.g. boolean operation: formula = ( Null == 0 ) = 1
• e.g. Sum: formula = Null + 0 = 0

where

• '0' represents the value '0'
• W represents any valid value
• O1 is a unary operator (such as NODIM, NOERR)
• O2 is a binary operator (like +, -, *)
##### Sample Query • green frame: Null values
• orange frame: zeros
##### Query Feature Exceptions and Null values

As explained in Query Feature Exceptions, with 7.31 SP10 a correction has been done (in Note 1883498) which, among others, had the intention to improve the performance of queries with large data sets as it deletes all empty(with Nulls) cells from the result set. However as a result the exceptions is no longer applied on NULL values. We recommend for those key figures where you have defined corresponding exceptions (and where you want to have the Null values treated the same way as zeros), to use a formula which adds a zero to your existing formula or key figure.

• No labels