Analytics Functions

Analytics functions allow you to explore data using models such as forecast, trendline, and cluster. Alternatively, you can drag and drop analytics functions into the workbook editor.

Alternatively, you can add forecasts, trendlines, and clusters to a workbook by selecting them on the Analytics tab of the Data Panel in the workbook editor. See Add Statistical Analytics Functions to Visualizations.

Function	Example	Description	Syntax
CLUSTER	`CLUSTER((product, company), (billed_quantity, revenue), 'clusterName', 'algorithm=k-means;numClusters=%1;maxIter=%2;useRandomSeed=FALSE;enablePartitioning=TRUE', 5, 10)`	Collects a set of records into groups based on one or more input expressions using K-Means or Hierarchical Clustering.	`CLUSTER((dimension_expr1 , ... dimension_exprN), (expr1, ... exprN), output_column_name, options, [runtime_binded_options])`
FORECAST	Revenue Forecast by Day Example This example selects revenue forecast by day. FORECAST("A - Sample Sales"."Base Facts"."1- Revenue" Target, ("A - Sample Sales"."Time"."T00 Calendar Date"),'forecast', 'numPeriods=30;predictionInterval=70;') ForecastedRevenue Revenue Forecast by Year and Quarter Example This example selects revenue forecast by year and quarter. FORECAST("A - Sample Sales"."Base Facts"."1- Revenue", ("A - Sample Sales"."Time"."T01 Year" timeYear, "A - Sample Sales"."Time"."T02 Quarter" TimeQuarter),'forecast', 'numPeriods=30;predictionInterval=70;') ForecastedRevenue	Creates a time-series model of the specified measure over the series using Exponential Smoothing (ETS), Seasonal ARIMA, ARIMA, or Prophet. This function outputs a forecast for a set of periods as specified by the numPeriods argument. See also additional FORECAST Function Options below.	`FORECAST(measure, ([series]), output_column_name, options,[runtime_binded_options])])` Where: measure represents the measure to forecast, for example, revenue data. series represents the time grain used to build the forecast model. The series is a list of one or more time dimension columns. If you omit series, then the time grain is determined from the query. output_column_name represents the valid column names of forecast, low, high, and predictionInterval. options represents a string list of name/value pairs separated by a semi-colon (;). The value can include %1 ... %N specified in `runtime_binded_options`. runtime_binded_options represents a comma separated list of columns and options. Values for these columns and options are evaluated and resolved during individual query execution time. See also additional FORECAST Function Options below.
OUTLIER	`OUTLIER((product, company), (billed_quantity, revenue), 'isOutlier', 'algorithm=kmeans')`	Classifies a record as Outlier based on one or more input expressions using K-Means or Hierarchical Clustering or Multi-Variate Outlier detection Algorithms.	`OUTLIER((dimension_expr1 , ... dimension_exprN), (expr1, ... exprN), output_column_name, options, [runtime_binded_options])`
REGR	`REGR(revenue, (discount_amount), (product_type, brand), 'fitted', '')`	Fits a linear model and returns the fitted values or model. This function can be used to fit a linear curve on two measures.	`REGR(y_axis_measure_expr, (x_axis_expr), (category_expr1, ..., category_exprN), output_column_name, options, [runtime_binded_options])`
TRENDLINE	`TRENDLINE(revenue, (calendar_year, calendar_quarter, calendar_month) BY (product), 'LINEAR', 'VALUE')`	Oracle recommends that you apply a Trendline using the Add Statistics property when viewing a visualization. See Adjust Visualization Properties. Fits a linear, polynomial, or exponential model, and returns the fitted values or model. The numeric_expr represents the Y value for the trend and the series (time columns) represent the X value.	`TRENDLINE(numeric_expr, ([series]) BY ([partitionBy]), model_type, result_type)`

FORECAST Function Options The following table lists available options to use with the FORECAST function.

Option Name	Values	Description
numPeriods	Integer	The number of periods to forecast.
predictionInterval	0 to 100, where higher values specify higher confidence	The confidence level for the prediction.
modelType	ETS (Exponential Smoothing) SeasonalArima ARIMA Prophet	The model to use for forecasting.
useBoxCox	TRUE FALSE	If TRUE, then use Box-Cox transformation.
lambdaValue	Not applicable	The Box-Cox transformation parameter. Ignore if NULL or when `useBoxCox` is FALSE. Otherwise the data is transformed before the model is estimated.
trendDamp	TRUE FALSE	This is specific to the Exponential Smoothing model. If TRUE, then use damped trend. If FALSE or NULL, then use non-damped trend.
errorType	Not applicable	This is specific to the Exponential Smoothing model.
trendType	N (none) A (additive) M (multiplicative) Z (automatically selected)	This is specific to the Exponential Smoothing model
seasonType	N (none) A (additive) M (multiplicative) Z (automatically selected)	This is specific to the Exponential Smoothing model
modelParamIC	ic_auto ic_aicc ic_bic ic_auto (this is the default)	The information criterion (IC) used in the model selection.