Trend and Forecasting

Contents[Hide]

The Trend and Forecasting function calculates a regression line or line of best fit. When applied to a time series, it can forecast future values.

This function can be used with a measure and date/time values, or with two measures where each is typically plotted along a separate chart axis.

Trend and Forecasting
Trend and Forecasting

There are four regression functions available:

1. Syntax

Trend and Forecasting:

TRENDFORE(d0,d1,s0,s1,s2)

Upper Band Error:

TRENDFOREUPPER(d0,d1,s0,s1,s2)

Lower Band Error:

TRENDFORELOWER(d0,d1,s0,s1,s2)

Axis:

TRENDFOREAXIS(d0,d1,s0,s1,s2)

2. Input

The Trend and Forecasting functions require the following input series:

  • d0 - Trend Axis values - The set of values on the trending axis (e.g. datetime axis).
  • d1 - Input data values - The set of historical data values to be used in the Trend and Forecasting formula.

3. Parameters

  • s0 - Regression Type - The type of regression function used by the Trend and Forecasting formula. Available choices include:
    • 0 - Linear (default)
    • 1 - Exponential
    • 2 - Logarithmic
    • 3 - Power
  • s1 - Regression Degree - The degree used by the regression function in calculating the Trend and Forecasting formula. Default value is 2.
  • s2 - Forecasting Period - The length of the forecasted data series as returned by the Trend and Forecasting formula. Available choices include:
    • 0 - No Forecast
    • 1 - Tenth Input
    • 2 - Quarter Input
    • 3 - Half Input (default)
    • 4 - Full Input

4. Output

The Trend and Forecasting functions generate the following outputs:

  • Trend and Forecasting - The Trend and Forecasting result set.
  • Upper Band Error - The upper error boundary based on standard deviation and the forecasting error.
  • Lower Band Error - The lower error boundary based on standard deviation and the forecasting error.

5. Examples

5.1. Linear regression on scatter plot

This example shows how to use the TRENDFORE function on a scatter plot chart. The dashboard editor is pictured below, but similar steps can be followed when editing a metric set.

First, create a new dashboard and add a Scatter Plot data visualization from the toolbar.

Add two measures (e.g., LineTotal, OrderQty) and one row hierarchy (e.g., OrderDate).

Set up a scatter plot chart
Set up a scatter plot chart

With the chart selected on the canvas, go to the toolbar, click Data Tools, and then select Add Formula.

In the formula bar, enter the TRENDFORE function and use the two measures as arguments.

Apply linear regression using TRENDFORE
Apply linear regression using TRENDFORE

Click output and choose the line chart type. (You can also right-click the new series later and re-visualize it to Line instead.)

Click apply when finished.

Trend line visualized as a line series
Trend line visualized as a line series

6. Notes

  • The forecast will only go ahead in time as far as the time dimension's end date.

7. See also

 

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours: 7am-6pm, ET, Mon-Fri