Technical data

ManualsBrandsSimplicity ManualsDo-It-Yourself tools4564

191

192

193

194

195

196

197

198

199

200

Using Built-in Functio ns in Analytic Models Chapter 11

Example

Suppose that a c

ube collection contains a data cube called SALES that uses dimensions called PEOPLE and

MONTHS. It a ls

o contains a data cube called MEDIAN_OF_SALES that contains the following formula for

calculating t

he median over time for each person:

MEDIAN(MONTH

S, SALES)

The cube collection also contains a data cube called MEDIAN_OF_SALES_IN_FIRST_6_MONTHS that

contains this formula:

MEDIAN(MONTH

S, SALES, MEMBER(MONTHS) <= 6)

MEMBER

Syntax

MEMBER(Dimen

sion)

Description

The MEMBER function returns the Member being calculated.

Returns

The member being calculated.

Example

The followin

g examples employ the MEMBER function:

Example 1

You can perform different calculations for different ranges of members b y comparing the MEMBER function

to a number. For exa mple, to perform a special calculation for the first six months, use the MEMBER

function with the IF function:

IF(MEMBER(MONTHS) <= 6, EXPR_FOR_1ST_6_MONTHS, EXPR_FOR_OTHER_MONTHS)

Example 2

You can perform special calculations for a particular member in a dimension by comparing the MEMBER

function to a member reference. For example, suppose that your company allocates the Administration

department’s expense equally to all of the other departments. Your analytic model contains a DEPARTMENTS

dimension, of which Admin is a member. Your analytic model also contains a data cube, TOTAL_EXPENSE,

that contains the total expense for each department. The following formula describes how you would calculate

the administration allocation for each department in a data cube called ADMIN_ALLOCATION:

IF(MEMBER(DEPARTMENTS) = [DEPARTMENTS:Admin], -TOTAL_EXPENSE, TOTAL_EXPENSE ⇒

[DEPARTMEN

TS:Admin] / (NUMMEMBERS(DEPARTMENTS) - 1))

The NUMMEMBERS function returns the number of m embers in a dimension. The key to this formula is that

the allocation for each department is the same except for Admin. The IF function checks which department is

being calculated. If the department is Admin, t he result is minus Total Expense s o that it backs out the expense

for the Admin department. If the department is not Admin, the result is Total Expense for Admin—notice the

data cube slice—divided by the number of departments other than Admin.