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 a 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.

Syntax

SELECT 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)

Examples

SELECT FORECAST(SUM(quantity) , 6)

Suppose you create a forecast fo rthe totals of 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)