05 Jan 2017

Generating Multiple Forecasts Using Time Series Tools in Alteryx

Ricardo Santos

Forecasting is a hot topic in today's analytical requirements for most companies. Alteryx has two-time series tools that can be used to produce forecasts: ARIMA (AutoRegressive Integrated Moving Average) and ETS (Exponential Smoothing Time Series). Given that these tools are only capable of generating one forecast measure at a time, Alteryx has just released a couple of new similar tools that enable the generation of multiple forecasts at once. This blog shows how these tools can be used to produce individual as well as multiple forecasts, through examples focusing on forecasting product sales.

Generating a Sales Forecast for a Product Category

To build the examples, we are using a CSV file materialised from one of Tableau's training datasets: the Sample – EU Superstore, which refers to individual product sales that can roll-up to categories and subcategories. Taking the EU Superstore dataset, we will build a sales forecast for the Office Supplies category for the period of the next seven days.

Start by including an Input tool that points to the Sample – EU Superstore.csv file.

Alteryx Input Tool

Alteryx Input Data

Alteryx Open a Data File

Add an Auto Field tool to get the adequate data types for each source column.

Alteryx Auto Field tool

Next, add a Select tool to choose only the relevant fields for the forecast (in this case, the Order Date, Category and Sales fields).

Alteryx Select tool

Alteryx Select Configuration

Now, filter the rows to select only those belonging to the Category = "Office Supplies" by adding a Filter tool to the workflow.

Alteryx Filter tool

Alteryx Filter Configuration

Next, we need to group the sales data to reflect the total sales for each product on a daily basis, since we intend to generate a daily forecast for the next seven days – bear in mind that you need to aggregate your data accordingly to the grain of the timely forecast, i.e., if you require a monthly forecast, you would need to aggregate your data to a monthly grain, if you require a quarterly forecast, you would need to aggregate your data to a quarterly grain, etc.

In order to do this, add a Summarize tool that aggregates the data accordingly (in this case, summarising the Sales grouped by Order Date; we don't need to add Category to the aggregation, because we have already previously used the Filter tool to obtain only the rows referring to "Office Supplies", making the aggregation perform faster as we're not wasting time aggregating irrelevant data).

Alteryx Summarize tool

Now, we can add the ARIMA or ETS tool, depending on which we intend to use (both have their own particularities – you can compare both forecasting techniques using test data and then see how accurately they forecasted their results against validation data in order to choose which is the best for your particular case), choosing the Sales field as the Target field and selecting the Daily frequency, for example.

Alteryx ETS tool

Alteryx ETS Configuration

Next, add a TS Forecast tool so that we can output the forecast results, changing the forecast field name to SalesForecast. Notice that you can choose the percentage for the larger and smaller confidence interval values of the forecast, as well as how many periods into the future. In our case, we want to predict the following seven days; since we defined the ETS tool to perform a Daily analysis forecast, we define the number of periods into the future to forecast as seven.

TS Forecast Tool

TS Forecast Configuration

Finally, you can add an Output tool to output the forecast. In this example, we chose to output it to a regular CSV file.

The complete Alteryx workflow can be seen in the following image, as well as the content of the forecast.

Alteryx Workflow Multiple Forecasts

EUSuperStorForecast CSV

Generating a Sales Forecast for each Product Sub-Category in the Dataset

Regrettably, the ARIMA and ETS tools only generate one forecast at a time, making it unfeasible to build a simple workflow using these tools to generate individual forecasts for each product sub-category, or even each product, if we have tens, hundreds or thousands of them.

Fortunately, Alteryx has solved this issue by making available the download of two additional time series tools since last October: the TS Model Factory and the TS Forecast Factory tools.

TS Forecast Factory  TS Model Factory

A sample workflow including these new tools can be downloaded from the Alteryx Analytics Gallery at https://gallery.alteryx.com/#!app/TS-Factory-Sample/5772b0ebaa690a1348cc6bcb.

To take advantage of these tools in the previously built workflow in order to produce a sales forecast for each product, perform the following steps:

1. Remove the Filter tool, so that all rows are now relevant for the forecasting
2. In the Select tool, untick the Category field and tick the Sub-Category field
3. Replace the ETS tool with the TS Model Factory tool, choosing which technique (ARIMA or ETS) to use and selecting the field to group the rows for each forecast by (i.e., to generate a forecast for each product subcategory, choose the Sub-Category as the grouping field).

TS Model Factory tool

Alteryx TS Model Factory Configuration

4. Replace the TS Forecast tool with the TS Forecast Factory tool, similarly to what was done in the previous original workflow with the TS Forecast tool.

Alteryx TS Forecast tool

And it's done. You should now have a workflow that looks like the one in the following figure, which is able to generate a sales forecast for each product subcategory, as shown.

Alteryx Workflow Multiple Forecasts Using Time Series Tools

EUSuperStorForecast CSV 2

The generated forecasts can now be used to feed a forecast database or integrated with reporting tools to display them.

 

Ricardo Santos

About the author

Lead BI Developer at Concentra

Related to Generating Multiple Forecasts Using Time Series Tools in Alteryx

Subscribe to our newsletter

Get in touch
Get the edge

Go to webform

Subscribe to our newsletter

Get in touch
Get the edge

Go to webform