As described in the context calculation overview, the advanced analytics functions are designed to provide an easy technique to applying advanced statistical and forecasting mathematics to the data points in a result set. These types of logical elements are seldom done with semantic formulas (model based )or granular calculations (row-level, transactional) because they almost always require context, and they will change to suit the selections made in a report or visual.
- The advanced analytic functions are an extension of the tools described here.
Advanced Analytics Logic
The core principal behind advanced analytic calc's is to read all the data points in a query and easily compute values using advanced statistical methods (forecasting and regression). Although these values can be derived in alternative ways, the key elements driving the math look at the list of items (from a hierarchy) and the selected metric(s). By changing the hierarchy or the selections, the computed values will change accordingly, without needing to redefine or recode the calculation itself (i.e. its contextual).
The contextual functions provided cover 2 areas. Other advanced analytical methods in addition to these are also accessible from the query ribbon.
- Forecasting - The Forecasting tools are used to project future values in a time series, such as corporate sales over the next four quarters.
- Regression - Regression functions allow users to compare two or more metrics across a range of items to see their relationship.
By right clicking on the "cost" metric chip and choosing forecast, the Discover tools add a new metric to the query calculating forecast. By default, it projects 6 periods forward use the "best fit" model - which runs all the in-built forecasting engines and picks the one with the best statistical fit to the data.
Notice that forecasting not only adds a new metric column, it also generates new "computed data".
Plotting this in a line chart, we see that the forecast is not only shown as a projection for the selected periods, but the "prediction interval bands" are also shown - to show scope of the forecast with 90% confidence.
To further tweak the forecasting logic driving the calculation, users should use the advanced settings in the Query tab 'forecasting' menu.
The simplest regression is to take a single metric across a list of items ("promotion programs" in this example) and generate a linear regression function. The generated values can be seen here in the grid, and are created by using the sales values only.
The more typical (and advanced) regression involves regressing two metrics against each other for a list of items. To do this, select both measures (either in the visual or from the elements tree) and choose the regression function.
This will generate the regression of the two metrics as shown below.
The easiest way to digest these computed values is typically in a graphical format. Here we are plotting sales vs quantity for each promotion. The red line represents the regression calculation for this dataset. The details of the regression (formula and R-squared) are shown in the legend.