Technical data

Chapter 11 Using Built-in Functions in Analytic M odels

Example 1

Suppose an anal

ytic model contains a data cube called ADVERTISING_BY_PRODUCT and a data cube called

UNITS_SOLD.B

oth data cubes use a dimension called PRODUCTS. Use the following formula to calculate

the average un

its sold for all products:

DAVG(PRODUCT

S, UNITS_SOLD)

The DAVG function does not include a condition, so the function averages UNITS_SOLD for all members in

the PRODUCTS dimension. Use the following formula to calculate the average units sold for all products

with advertising of at least USD 10,000:

AVG(PRODUCTS

, UNITS_SOLD, ADVERTISING_BY_PRODUCT >= 10000)

In this case, the function averages UNITS_SOLD only for the products where ADVERTISING_BY_PRODUCT

is greater than or equal to 10000.

Example 2

You can mak e th

e analysis more flexible by creating a dimension called RANGES and attaching it to

AVG_UNITS_S

OLD. Define a new data cube called AD_COST_MIN that uses the RANGES dimension. Each

number in AD_

COST_MIN defines the m inimum value for the range, while the next number defines the upper

limit for the

range. Calculate the average units sold for each range as follows:

DAVG(PRODUC

TS, UNITS_SOLD, ADVERTISING_BY_PRODUCT >= AD_COST_MIN .AND. ADVERTISING_⇒

⇒

BY_PRODUCT <

NEXT(RANGES, AD_COST_MIN))

For each range, the formula averages only those produ cts whose advertising cost is greater than or equal

to the current AD_COST_MIN and less than the next AD_COST_MIN. (The NEXT function returns

AD_COST_MIN for the n ext member in the RANGES dimension.)