Technical data

Using Built-in Functio ns in Analytic Models Chapter 11
You normally use the BREAK function within an IF function to break out of a loop when a specified condition
is achieved. To return Just right from the formula, ENOUGH_ALREADY must contain a value from the
sequence 2, 6, 14, 30, and so on.
CASE
Syntax
CASE(Condition A : Result A, Condition B : Result B {,...})
Description
The CASE funct
ion returns the Result that corresponds to the first true Condition; if none of the conditions
is true, it re
turns zero.
Returns
The Result that corresponds to the first true Condition; if none of the conditions is true, it returns zero.
Example
Suppose a company awards its salespeople the following commissions:
• A 10 percent c
ommission if their sales are at least 50,000 USD.
•An8percentc
ommission if their sales are at least 30,000 USD.
• A 5 percent com
mission if their sales are at least 15,000 USD.
You can calculate the commission rate for a salesperson with the following formula:
CASE(SALES >= 50000 : 0.10, SALES >= 30000 : 0.08, SALES >= 15000 : 0.05)
If SALES is 45000, this formula returns 0.08. Notice that the CASE function returns the result for the first true
condition, even if some of the remaining conditions are true.
The above for
mula returns zero if SALES is less than 15000. Suppose that the company awards a 3 percent
commission o
n all sales under 15,000 USD. You can model this with the following formula:
CASE(SALES >= 50000 : 0.10, SALES >= 30000 : 0.08, SALES >= 15000 : 0.05, #DEFAULT⇒
: 0.03)
The last condition (#DEFAULT) is always equivalent to TRUE, so the CASE function returns 0.03 if SALES
is less than 15000. If you want the CASE function to return a default value other than zero, use #DEFAULT as
the last condition.
CHANGE
Syntax
CHANGE(Dimension, Data, {Count})
Description
The CHANGE fu
nction returns the difference between the v alue of Data for the member being calculated and
the value of
Data for Count members back. If Count is omitted, it is assumed to be 1.
144 Copyright © 1988-2007, Oracl e. All rights reserved.