Calculating Trends and Predicting Values With Multivariate REGRESS

How to:

Reference:

You can calculate trends and predict values with multivariate regression. This method derives a linear equation that best fits a set of numeric data points, and uses this equation to create a new column in the report output. The equation can be based on one to three independent variables.

This method estimates values by assuming that the dependent variable (y, the new calculated values) and the independent variables (x1, x2, x3) are related by the following linear equation:

y = a1*x1 [+ a2*x2 [+ a3*x3]] + b 

When there is one independent variable, the equation represents a straight line. This produces the same values as FORECAST using the REGRESS method. When there are two independent variables, the equation represents a plane, and with three independent variables, it represents a hyperplane. You should use this technique when you have reason to believe that the dependent variable can be approximated by a linear combination of the independent variables.

REGRESS uses a technique called Ordinary Least Squares to calculate values for the coefficients (a1, a2, a3, and b) that minimize the sum of the squared differences between the data and the resulting line, plane, or hyperplane.


Top of page

x
Procedure: How to Create a Multivariate Linear Regression Column Using Report Painter
  1. In the Report Painter main window, select a numeric BY field.
  2. Click the Forecast button.
  3. From the Step 1: Choose a Method drop-down list, select the Multivariate Regression option.
  4. From the Step 2: Choose the First Independent Variable drop-down list, select a variable. You have the option to choose additional independent variables in Step 3 and Step 4.
  5. From the Step 5: Choose the Dependent Variable drop-down list, select a dependent variable.
  6. Click OK.

Top of page

x
Syntax: How to Create a Multivariate Linear Regression Column Using WebFOCUS Language
ON {sortfield} RECAP y[/fmt] = REGRESS(n, x1, [x2, [x3,]] z);

where:

sortfield
Is a field in the data source. It cannot be the same field as any of the parameters to REGRESS. A new linear regression equation is derived each time the sort field value changes.
y
Is the new numeric column calculated by applying the regression equation. You cannot DEFINE or COMPUTE a field with this name.
fmt
Is the display format for y. If it is omitted, the default format is D12.2.
n
Is a whole number from 1 to 3 indicating the number of independent variables.
x1, x2, x3
Are the field names to be used as the independent variables. All of these variables must be numeric and be independent of each other.
z
Is an existing numeric field that is assumed to be approximately linearly dependent on the independent variables and is used to derive the regression equation.

Top of page

x
Reference: Usage Notes for REGRESS


Example: Creating a Multivariate Linear Regression Column

The following example uses the GGSALES data source to calculate an estimated DOLLARS column. The BUDUNITS, UNITS, and BUDDOLLARS fields are the independent variables. The DOLLARS field provides the actual values to be estimated.

  1. Create a new procedure and open the ggsales.mas file in Report Painter.
  2. Select Report at the top of the Report Painter window, then select Define from the drop-down list.

    The Define dialog box opens.

  3. Enter MONTH in the Field input area, type M (a date format) in the Format input area, then double-click DATE in the Fields List to add it to the area below PERIOD, then click New.
  4. Enter PERIOD in the Field input area, type I2 (Integer format) in the Format input area, then type MONTH in the area below PERIOD, then click OK.

    The Define dialog box closes and you return back to Report Painter.

  5. Add the PERIOD, BUDUNITS, UNITS, BUDDOLLARS, and DOLLARS fields to the report.
  6. Select the PERIOD field, and click the By button.
  7. Click the Where/If button.

    The Report Options dialog box opens.

  8. Enter CATEGORY EQ 'Coffee' in the Expression field, and click New.
  9. Enter REGION EQ 'West' in the Expression field, and click New.
  10. Enter UNITS GT 1600 AND UNITS LT 1700 in the Expression field, and click OK.
  11. Click the Forecast button.

    The Forecast window opens.

  12. In the Field Name field, enter EST_DOLLARS.
  13. From the Step 1: Choose a Method drop-down list, select Multivariate Regression.
  14. From the Step 2: Choose the First Independent Variable drop-down list, select BUDUNITS.
  15. From the Step 3: Choose Another Independent Variable drop-down list, select UNITS.
  16. From the Step 4: Choose Another Independent Variable drop-down list, select BUDDOLLARS.
  17. From the Step 5: Choose the Dependent Variable drop-down list, select DOLLARS.
  18. Click OK.

When the report is run, the multivariate regression values appear in the EST_DOLLARS column. The output is:

output

TABLE FILE GGSALES
PRINT BUDUNITS UNITS BUDDOLLARS DOLLARS
ON TABLE RECAP ESTDOLLARS/F8 = REGRESS(3,BUDUNITS, UNITS, BUDDOLLARS, 
DOLLARS);
WHERE CATEGORY EQ 'Coffee'
WHERE REGION EQ 'West'
WHERE UNITS GT 1600 AND UNITS LT 1700
END

WebFOCUS