SLOPE

Syntax

SLOPE (Dimension, Y, X, {Condition})

Description

The SLOPE function 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. If Condition is included, the function fits the line only to those members that meet the condition.

Use the SLOPE function together with the INTERCEPT function to find the line with the closest fit to a set of points. You can use these functions to analyze a historical trend, and then use the trend to make forecasts. You can also use these functions to analyze the relationship between different variables, such as sales and travel expense.

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. If Condition is included, the function fits the line only to those members that meet the condition.

Example

The following sections provide examples of analyzing a historical trend and analyzing a relationship between data cubes.

Analyzing a Historical Trend

To analyze a historical 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_DATE)

    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 by 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 MEMBER.

  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. You 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 the Relationship Between Two Data Cubes

To analyze the relationship between two data cubes:

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

    SLOPE(DIMENSION, DEPENDENT_VARIABLE, INDEPENDENT_VARIABLE)

    DEPENDENT_VARIABLE 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 restrict the analysis to selected members of DIMENSION by using a condition for the fourth argument.

  2. Calculate the intercept for the relationship line with this formula for the RELATION_START cube:

    INTERCEPT(DIMENSION, 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

Example 1: Analyzing a Historical Trend

Suppose that you want to analyze the trend in historical sales to forecast future sales. The historical sales are stored in a data cube called ACTUAL_SALES that uses a dimension called MONTHS. The date of the last actual value is stored in a data cube called LAST_ACTUAL_DATE. Calculate the sales trend with the following formulas:

  • TREND_SLOPE data cube formula:

    SLOPE(MONTHS, ACTUAL_SALES, MEMBER(MONTHS), 
MEMBER(MONTHS) <= LAST_ACTUAL_DATE)

  • TREND_START data cube formula:

    INTERCEPT(MONTHS, ACTUAL_SALES, MEMBER(MONTHS), 
MEMBER(MONTHS) <= LAST_ACTUAL-DATE)

  • SALES_TREND data cube formula:

    TREND_START + TREND_SLOPE * MEMBER(MONTHS)

    Note:

    TREND_SLOPE and TREND_START do not use the MONTHS dimension.

Example 2: Analyzing the Relationship Between Data Cubes

Suppose that you want to analyze how UNITS_SOLD has affected SUPPORT_COSTS. Both of these data cubes use a dimension called MONTHS. The date of the last actual value is stored in a data cube called LAST_ACTUAL_DATE. Enter the estimates for future unit sales in a data cube called SALES_FORECAST, and then calculate the resulting SUPPORT_FORECAST data cube as follows:

  • RELATION_SLOPE data cube formula:

    SLOPE(MONTHS, SUPPORT_COSTS, UNITS_SOLD, 
MEMBER(MONTHS) <= LAST_ACTUAL_DATE)

  • RELATION_START data cube formula:

    INTERCEPT(MONTHS, SUPPORT_COSTS, UNITS_SOLD, 
MEMBER(MONTHS) <= LAST_ACTUAL_DATE)

  • SUPPORT_FORECAST data cube formula:

    IF(DATE( ) > LAST_ACTUAL_DATE, 
RELATION_START + SALES_FORECAST * RELATION_SLOPE, 0)

Notice that this example uses a different approach than the previous example. In the first example, you analyzed how sales were affected by time, and then used the results to predict future sales based on the passage of time. In this example, you analyzed how support was affected by sales, and then used the results to predict future support costs based on future sales.