Technical data

ManualsBrandsSimplicity ManualsDo-It-Yourself tools4564

191

192

193

194

195

196

197

198

199

200

Chapter 11 Using Built-in Functions in Analytic M odels

See the entries for DAVG and MEMBER if you are unfamiliar with these functions. For each MONTH_NUM

member in AVG_SALES_BY_MONTH, the formula averages all Monthly Sales for which the month of the

year equals the index of the MONTH_NUM member. Thus, if the analytic calculation engine calculates the

fifth MONTH_NUM member for AVG_SALES_BY_MONTH, it averages the sales for the dates 2004/05/03,

2004/05/04, and 2004/05/05, because these are the dates for which the MONTH( ) function returns 5.

See Also

Chapter 11, “U

sing Built-in Functions in Analytic Models,” DAVG, page 148 and Chapter 11, “Using Built-in

Functions i n A

nalytic M odels,” MEMBER, page 172.

Syntax

NEXT(Dimens

ion, Data, {Count})

Description

The NEXT function returns the value of Data from Count members forward in Dimension.IfCount is

omitted, it is assumed to be 1.

Note. The NEXT function operates on detail member names that are persisted in the main record. This

function does not use trees to determine the order of members.

Returns

The value of D

ata from Count members forward in Dimension.IfCount is omitted, it is assumed to be 1 .

Example

To refer to the next month’s sales in a rule, use NEXT(MONTHS, SALES).

The NEXT function can be used together with the CUMAVG function to calculate a centered moving average ,

such as the average sales for the six months before and after a given month. The centered moving average

gives a sense of the normal monthly value for the year surrounding a particular month. You can then compare

the actual monthly value to the normal monthly value to see how seasonality affected the sales. Thus, if the

actual monthly value for August is higher than the normal monthly value for the year surrounding August,

this may indicate that sales tend to be higher than average in August.

Suppose tha

t the actual monthly sales are stored in a data cube called ACTUAL_SALES. Calculate the

CENTERED_A

VG_SALES cube as follows:

NEXT(MONTH

S, CUMAVG(MONTHS, ACTUAL_SALES, 13), 6)

This formula looks six months ahead (NEXT(MONTHS, ..., 6)), and then calculates the cumulative

average of the 13 months of sales preceding that time (CUMAVG(MONTHS, ACTUAL_SALES, 13)). For

example, when the analytic calculation engine calculates CENTERED_AVG_SALES for 2005/03, it looks

ahead six months to 2005/09, and then calculates the average sales for the 13 months preceding 2005/09.

Thus, the analytic calculation engine calculates the average sales for 2004/09 to 2005/09, which is the year

surrounding 2005/03.

Actually, this formula is not quite complete. You cannot calculate accurate results for the first six months or

the last six months of the analytic m odel because the analytic calculation engine is unable to look six months

back and six months ahead during those months. Therefore, the formula should return zero for those months:

IF(MEMBER(MONTHS) > 6 .AND. MEMBER(MONTHS) <= NUMMEMBERS(MONTHS) - 6, NEXT(MONTHS,⇒

CUMAVG(MO

NTHS, ACTUAL_SALES, 13), 6), 0)