Technical data

ManualsBrandsSimplicity ManualsDo-It-Yourself tools4564

211

212

213

214

215

216

217

218

219

220

Using Built-in Functio ns in Analytic Models Chapter 11

Analyzing a Historical Trend

To analyze a his

torical trend:

1. Calculate the slope for the trend line with this formula for the TREND_SLOPE cube:

SLOPE(DATE_DIMENSION, HISTORICAL_DATA, MEMBER(DATE_DIMENSION), MEMBER(MONTHS) <=⇒

LAST_ACTUAL_D

ATE)

HISTORICAL_DATA is the data cube that you want to analyze. DATE_DIMENSION is the

dimension used by the data cube, which is normally a date dimension. Because you want to know how

HISTORICAL_DATA is affected b y time, use the date index—MEMBER(DATE_DIMENSION)—as

the independent (X) argument. LAST_ACTUAL_DATE is a data cube containing the last date that

you want to analyze. If you want to analyze all of the dates in DATE_DIMENSION, you may omit

the condition.

See C

hapter 11, “Using Built-in Functions in Analytic Models,” MEMBER, page 172 .

2. Calculate the intercept for the trend line with the following formula for the TREND_START cube:

INTERCEPT(DATE_DIMENSION, HISTORICAL_DATA, MEMBER(DATE_DIMENSION), MEMBER(MONTHS)⇒

<= LAST_ACTUAL_DATE)

3. Yo u can now calculate the values for the trend line with the following formula for the

TREND_VALUES cube:

TREND_START + TREND_SLOPE * MEMBER(DATE_DIMENSION)

Analyzing th

e Relationship Between Two Data C ubes

To analyze the relationshipbetweentwodatacubes:

1. Calculate the slope for the relationship line with this formula for the RELATION_SLOPE cube:

SLOPE(DIMENSION, DEPENDENT_VARIABLE, INDEPENDENT_VARIABLE)

DEPENDENT_V

ARIABLE is the variable whose values are influenced by INDEPENDENT_VARIABLE.

For example,

if you want to know how sales are influenced by advertising, SALES is the dependent

variable and

ADVERTISING is the independent variable. If necessary, you may res trict the analysis to

selected mem

bers of DIMENSION by using a condition for the fourth argument.

2. Calculate th

e intercept for the relationship line with this formula for the RELATION_START cube:

INTERCEPT(D

IMENSION, DEPENDENT_VARIABLE, INDEPENDENT_VARIABLE)

If you included a condition in the formula for RELATION_SLOPE, be sure to include it in this

formula as well.

3. Given an independent variable, you can now estimate a corresponding dependent value with this

formula for the DEPENDENT_VALUE cube:

RELATION_START + INDEPENDENT_VALUE * RELATION_SLOPE

Returns

The slope of the line that has the closest fit to the points represented by Y and X. (The slope is the change

in Y divided by the change in X.) If Condition is omitted, the function fits the line to all of the members in

Dimension.IfCondition is included, the function fits the line only to those members that meet the condition.