TrendV (Trend, Vector Input)

The TrendV function uses data recorded for certain time intervals to calculate and display the expected value of data for an unknown time period. This is most commonly used to predict future metric values based on historical data. For example, based on revenue data for three years, you want to predict what your Revenue will be for the coming year.

You can use TrendV to predict values for any point in time, including dates that have known data. However, this type of function is meant for predicting future, unknown values.

This function uses a linear equation to predict future values. Therefore, it is best suited to predict values that increase or decrease in a linear trend. For example, if your profits are increasing by $10,000 each year, this is a scenario that is well suited for a prediction based on a linear equation.

For a function that predicts values in a similar way for data that is increasing or decreasing exponentially, see GrowthV (Growth, Vector Input).

Syntax

TrendV <FactID, SortBy>(KnownData, FutureTimeOffset)

Where:

KnownData is an object representing a list of values. This list of values is the known data to use to predict a future value for. KnownData is commonly used for metrics that represent data such as revenue, profit, and so on.

FutureTimeOffset is the point in time after the final time interval available for which to predict the data. This is commonly defined as an integer. For example, to predict values for the year 2009 when the last available data you have is for 2007, you would enter the value of 2 for FutureTimeOffset.

The TrendV function assumes that data is available for sequential time intervals with no missing data. If your data meets these requirements, this allows you to use a single expression to use on reports with varying time intervals such as year, month, quarter, and so on. This is an advantage of TrendV in relation to ForecastV, which requires you to specify a time interval such as year for each expression. For an example, see the examples described below.

FactID is a parameter that forces a calculation to take place on a fact table that contains the selected fact.

SortBy is a parameter that defines the order of calculation. For more information, see BreakBy and SortBy Parameters.

Example

The TrendV function is useful to display predicted future values of a metric as a subtotal of the metric on a report.

For example, in the MicroStrategy Tutorial project a new subtotal named 2008 Trend can be created with the following definition:

TrendV(x, 1)

Since this is in a subtotal, the x is a placeholder for a metric. When a subtotal is applied to a metric on a report, the placeholder inputs that metric’s values.

Also, 1 is used as the offset in the time interval to predict data for the first time interval that is not known.

After making the new custom subtotal available for the Revenue metric (see the Advanced Reporting Guide for steps to make custom subtotals available for metrics) a report with the attribute Year and the metric Revenue is created. When the 2008 Trend is displayed on the report, it displays the predicted value for 2008 Revenue, as shown below:

This value is the same value predicted in the example using ForecastV, as described in ForecastV (Forecast, Vector Input). The advantage of using TrendV is that since you do not have to define the attribute or other object that defines the time interval, you can switch the time interval used by modifying the report. For example, the report can be modified to display Quarter instead of Year, which allows the 2008 Trend subtotal to predict the revenue for the first quarter of 2008. This is shown in the report below.

The type of subtotal shown above could also be created using ForecastV, but it would require you to create a separate subtotal that used the Quarter attribute to define the time intervals.