Blogs / Self-Service BI / Alteryx / How to Forecast using Error Trend Seasonal(ETS) Tool in Alteryx

# How to Forecast using Error Trend Seasonal(ETS) Tool in Alteryx

Sep 17, 2019

Forecasting refers to predicting what will happen in the future by taking into consideration the events in the past and present. Basically, it is a decision-making tool that helps businesses cope with the impact of the future’s uncertainty by using historical data and trends. These estimates are projected into the coming months or years using one or more techniques such as Box-Jenkins models, Delphi method, exponential smoothing, moving averages, regression analysis, and trend projection. Here, we are going to learn about ETS Tool in Alteryx. This tool uses an exponential smoothing method for forecasting.

### How does ETS Model work?

ETS calculates or predicts a future value based on existing (historical) values by using the AAA version of the Exponential Smoothing algorithm. The predicted value is a continuation of the historical values in the specified timeline. This model is using three parameters – Error, Trend and Seasonal.

For each parameter, we have 4 values,

M – Multiplicative

N – None

Z – Auto

So, ETS (A, M, N) means –  Additive for Error, Multiplicative for Trend and None for Smoothening.

From the ETS model above, there are 15 models with additive errors and 15 models with multiplicative errors. In time series analysis it is possible to have some models that meet the assumptions. Related to that, to determine the best model of 30 ETS model can be used several criteria such as Akaike’s Information Criterion (AIC), Akaike’s Information Criterion correction (AICc) and Bayesian Information Criterion (BIC).

1. AIC – Given a collection of models for the data, AIC estimates the quality of each model, relative to each of the other models. Thus, AIC provides a means for model selection. AIC can be calculated using the following equation:

where LL – Log Likelihood, t – Total Parameters, T – No. of Observations.

2. AICc – When the sample size is small, there is a substantial probability that AIC will select models that have too many parameters, i.e. that AIC will overfit. To address such potential overfitting, AICc was developed. AICc is AIC with a correction for small sample sizes. AICc can be calculated as,

where is a bias correction, k – Estimation of parameter model, n – No. of Observation.

3. BIC – BIC is similar to the formula for AIC, but with a different penalty for the number of parameters. With AIC the penalty is 2k, whereas with BIC the penalty is ln(n) k. BIC can be calculated using the below formula;

where LL – Log-likelihood, k – Estimation of parameter model, n – No. of Observation.

Based on the given data, all the parameters will be taken automatically. Choose the best ETS model based on the smallest AIC, AICc and BIC values for each prediction.

### How to Configure the ETS Tool in Alteryx?

Let’s take a scenario, we need to forecast the spend of 2019. Data is there from 2010 to 2018 and each year has two spend values H1(First half of the year) Spend and H2(second half of the year) Spend.

Data is consumed into Alteryx using Text Input Tool. For ease of use, I would like to change it to a crosstab. Using Crosstab Tool, the data is changed as below,

As 2019 full year spend value needs to be predicted, periodic values also should have the yearly data. For this, we need to found a total spend for each year.

Using the formula tool, full-year spend value is calculated.

Now, the 2019 year spend is to be predicted. ETS Tool (Time Series Category) is inserted into the canvas. To configure the tool,

1. The model name is to be given.

2. Select the Target field – Field that is to be predicted. Total Spend is selected from the dropdown.

3. Set the frequency – Daily, Monthly or Annually etc. As we have data on a yearly basis. Annually option is selected.

4. Now values are set to Error, Trend and Seasonal type. As we discussed earlier, values can be set to any combinations.

5. In Other options tab, we can select the criteria ( (i.e). AIC, AICc or BIC), transformations and periods. If the Auto option is selected then the AICc is used if there are 48 or fewer observations of the target, otherwise, the AIC is used.

6. The number of periods to include in the forecast plot:This option results in a plot that contains the original data and a number of forecast future points along with 80% and 95% confidence intervals (A confidence interval is an estimate of the statistical uncertainty of the estimated parameters in the model.) around the forecast points. The user can specify the number of periods that should be forecast into the future for the plot.

7. Select Week Format: This allows the user to choose a method to specify work weeks. These options relate to what constitutes the first week of the year, and what day of the week a week begins on.

US – Sunday is the first day of the week

UK – Monday is the first day of the week

ISO8601 – Monday is the first day of the week

8. ETS Tool has three output anchors,

• O anchor – consists of an output stream containing the ETS model object that can be used for forecasting.
• R anchor: Consists of the report snippets generated by the ETS tool.
• I anchor: An interactive HTML dashboard consisting of plots and metrics.

9. This ETS tool gives an output of BLOB.

BLOB – is a data type that can store binary objects or data. Binary large objects are used in databases to store binary data such as images, multimedia files and executable software code. Generally, database software classifies binary large objects into two types: semi-structured data and unstructured data. XML files are categorized as semi-structured data, whereas images and multimedia data are unstructured data types.

To consume, convert this into a time series we need to use the TS tool. O anchor of the ETS tool is given to the TS tool. TS tool configuration is given below;

The overall workflow looks like this;

And the output is;

Forecast – Actual value forecasted based on the given data.

Other values – Values lies around true value based on the given confidence level.

Hope this helps you to understand the ETS model in Alteryx. ARIMA tool will be covered in a subsequent blog. Watch this space for more information.

Read more about similar Self Service BI topics here and learn more about Visual BI Solutions Microsoft Power BI offerings here.

Corporate HQ:
5920 Windhaven Pkwy, Plano, TX 75093

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192

solutions@visualbi.com 