NEXT

Syntax

NEXT (Dimension, Data, {Count})

Description

The NEXT function returns the value of Data from Count members forward in Dimension. If Count 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 Data from Count members forward in Dimension. If Count 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 that the actual monthly sales are stored in a data cube called ACTUAL_SALES. Calculate the CENTERED_AVG_SALES cube as follows:

NEXT(MONTHS, 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 model 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(MONTHS, ACTUAL_SALES, 13), 6), 0)

The condition of the IF statement ensures that the month being calculated is after the first six months and before the last six months of the analytic model. If the condition is true, the IF function returns the centered moving average calculated by the second argument; otherwise, the IF function returns zero.