Technical data

ManualsBrandsSimplicity ManualsDo-It-Yourself tools4564

171

172

173

174

175

176

177

178

179

180

Using Built-in Functio ns in Analytic Models Chapter 11

See Also

Chapter 11, “Using Built-in Functions in Analytic Models,” F OR, page 156 and Chapter 11, “Using Built-in

Functions i n Analytic Models,” WHILE, page 197.

DLOOKUP

Syntax

DLOOKUP(Dimension, Data, Condition, {Direction})

Description

The DLOOK UP fu

nction returns Data for the first Member in Dimension where Condition is true. If Direction

is omitted or z

ero, the function scans forward from the first member. If Direction is nonzero, the function scans

backward from

the last member. If no member in Dimension fulfills the Condition, the function returns zero.

Returns

Data for the first member in Dimension where Condition is true. If Direction is omitted or zero, the function

scans forward from the first member. If Direction is nonzero, the function scans backward from the last

member. If no member in Dimension fulfills the Condition, the function returns zero.

Example

Suppose that a company awards its salespeople a 10 perc ent commission if their sales are at least USD 50,000,

an 8 percent commission if their sales are at least USD 30,000, a 5 percent commission if their sales are at least

USD 15,000, and a 1 percent commission if their sales are less than USD 15,000. One way to calculate the

commission is to create a lookup table. Define a dimension called RANGES andattachittodatacubescalled

SALES_MINIMUM and LOOKUP_RATE. Each number in SALES_MINIMUM defines the minimum value

for the sales range, while the next number defines the upper limit for the range. LOOKUP_RATE holds the

commission rate for each range. Use the following formula to calculate the commission rate:

DLOOKUP(RANGES, LOOLUP_RATE, SALES >= SALES_MINIMUM, 1)

Because the

last argument of DLOOKUP is 1, the function starts with the last member of RANGES and scans

backwards u

ntil SALES is greater than or equal to SALES_MINIMUM. It is important to scan backwards

to find the

highest lookup rate for which the condition is true. Otherwise, the formula returns the lowest

lookup rat

e no matter how high the value of SALES is.

DMAX

Syntax

DMAX(Dime

nsion, Data, {Condition})

Descripti

The DMAX function returns the maximum of Data for the members in Dimension where Condition is True.

If Condition is omitted, DMAX returns the maximum of Data for all members in Dimension.IfData is

omitted, DMAX returns the maximum of the data cube being calculated, for all members up to the current

member in Dimension.