Technical data

ManualsBrandsSimplicity ManualsDo-It-Yourself tools4564

211

212

213

214

215

216

217

218

219

220

Chapter 11 Using Built-in Functions in Analytic M odels

To understand the THIS function, you need to understand how database functions work. A database function

scans the members in a dimension to calculate a result. For example, suppose that you define the following

formula:

DAVG(PRODUCTS, ADVERTISING, SALES > 50000)

This formula c

alculates the average advertising for products with sales over 50,000 USD. The DAVG

function scan

s the PRODUCTS dimension and evaluates the condition SALES > 50000 for e ach product.

If the conditi

on is true, the DAVG function includes the Advertising for that product in the average. The

important po

int here is that the DAVG function evaluates the condition (SALES > 5 0000) and the expression

(Advertisin

g) for the product being scanned.

Now suppose that you want to calculate the following result for each product: the average advertising for

products whose sales are greater than the product being calculated.

Create a data cube called AVG_ADVERTISING_FOR_BETTER_PERFORMERS that uses the PRODUCTS

dimension. Its formula should look similar to:

DAVG(PRODUCTS, ADVERTISING, SALES > "Sales for the product being calculated")

To get the sales for the product being calculated, remember that the DAVG function uses the sales for the

product being scanned. The solution is to use the THIS function:

DAVG(PRODUCTS, ADVERTISING, SALES > THIS(SALES))

The THIS function forces the DAVG function to use the sales for the product being calculated. Thus, the

condition compares the sales for the product being scanned to the sales for the product being calculated. If the

condition is true, the DAVG function includes the Advertising for the product being scanned.

Returns

The value of

Expression for the members being calculated, even if Expression is used in a database function.

THISCUBE

Syntax

THISCUBE()

Descriptio

The THISCUBE function returns a reference to the current calculating data cube in a user function.

Returns

A reference to the current calculating data c ube in a user function.

Example

CHANGE(MON

THS, THISCUBE())

The user function in this example calculates the monthly change for each data cube and is used inside

an aggregate override user function that affects the SALES, COST_OF_GOODS, and GROSS_MARGIN

data cubes.

In this example, the analytic calculation engine performs the same as if you entered these three functions:

• CHANGE(MO

NTHS, SALES)

• CHANGE(MO

NTHS, COST_OF_GOODS)