FORECAST - Add a Trend Line to a Report

FORECAST functions returns linear regression estimates for up to 10 future periods based on existing data. Second parameter is number of periods; default is 3.

Use the FORECAST function to add a trend line to a report. The trend line uses linear regression to predict future periods based on existing data. For example, you could use a trend line to predict future sales based on historical data. Forecast reports must have one date/time attribute on the X axis, and no other attributes.

When you create a report with FORECAST, you can select one date/time attribute only in the How pane.

Trend line

Syntax

FORECAST(... [, number of periods])

Optionally specify a number of periods to forecast in the future.

Format

number

Default: 3

All report filters are applied to the metric that you FORECAST except for date and time filters. To apply a date or time filter to the report, add it directly to the metric. For example:

SELECT FORECAST((SELECT COUNT(Activity) WHERE Year = 2012), 6)

If the metric being FORECAST contains a BY clause, the trend line may render in steps. To create a smooth regression line, remove the BY clause.If your FORECAST report does not have a trend line, verify that you have selected a date or time attribute in the How pane.

Examples

Suppose you create a report to forecast the totals of the Deal Size fact sliced by the Month (Sale Closed) attribute. Use the following metric to compute the next three months based on all historical data:

SELECT FORECAST(SUM(Deal Size))

This metric predicts Deal Size for the next six months:

SELECT FORECAST(SUM(Deal Size), 6)

This metric predicts Deal Size for the next two months using data from the previous year:

SELECT FORECAST((SELECT SUM(Deal Size) WHERE year = PREVIOUS), 2)

This metric predicts Deal Size for the next two months using data from the previous and current years:

SELECT FORECAST((SELECT SUM(Deal Size) WHERE year = PREVIOUS OR year = THIS), 2)

This metric predicts Deal Size for the next four months using data from the last twelve months:

SELECT FORECAST((SELECT SUM(Deal Size) WHERE month/year >= THIS-12), 4)