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.
When you create a report with FORECAST, you can only select one date/time attribute only in the How pane.
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)
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
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)