Time Series Forecasting

[Rhitik_Krishnani]

Hello Everyone,

Please help me in the time series forecasting with the attached data-set

RETAIL STORE QTY FORECASTING

I want to forecast quantity sold in each month for each item name in 2022 (Jan- Dec)

and append it with my input file

I want to use ARIMA and ETS and forecast the value

& also compare both the models using TS Compare.

If possible, please don't use macro and advance logic's because I am new to Alteryx and I really need your help guys for completing the use case.

You can also modify the use-case to make it more better and relevant.

Thank You

Rhitik Krishnani

[JoeS]

Hi @Rhitik_Krishnani 

There is a great sample that comes when you install the predictive tools (have you done that?)

It should be saved here, if you have the admin version installed: C:\Program Files\Alteryx\Samples\en\02 Learn_one_tool_at_a_time\14 Time Series\ETS_ARIMA_TSCompare_TSForecast.yxmd

That covers; some data prep, ETS, ARIMA, Compare and then onto Forecast so should be great for getting you started with what you need.

[CharlieS · Accepted solution]

Hi @Rhitik_Krishnani 

I've put together this example to get you started using these tools for your use case. That being said, this is just a tool example, and the model selection and configuration will be up to you. I started by using a Select tool to remove some unnecessary fields. Quantity is our target variable for each sub-category, so I removed [Sales] and [Inventory]. Now that we have our data ready, let's get to the Time Series tools. 

The important thing to understand about the TS Compare tool is that you will be comparing forecasts and actual values for the same time period, so your firecasts will be generated not using all the available data. This is why I use a filter to remove the last year of data (like a holdout sample). Then an ETS and ARIMA model are trained using the respective tools, and those model objects are input to the TS Compare tool along with the holdout data so you can see all side-by-side. 

After spending time configuring the model you want to use, you can copy that tool and use a TS Forecast tools to produce the estimated values for subsequent periods (2022). I put these processes in separate toll containers with notes to keep things organized. 

You'll notice that there's a filter so that this only works on one category: this was done because these Time Series tools do not have built-in "group by" functionality. You can build this function in Alteryx Designer by using a batch macro, but I avoided that since you asked not to. 

Hopefully this shows a little of how these tools can be applied to your scenario and moves you forward in your analytic journey. Let us know what you think and reach out with any questions. 

[Rhitik_Krishnani]

@CharlieS 

If I have to see for other category, I have to change the category filter and run the flow again

Is there any better alternative for this ?

~Thank You

[CharlieS]

---

@Rhitik_Krishnani wrote:

@CharlieS 

If I have to see for other category, I have to change the category filter and run the flow again

Is there any better alternative for this ?

~Thank You

---

Yes, I would use a batch macro that runs this set of tools for each value in the [Sub-category] field. I did not build this in my initial reply as 1) you request that macros not be used and 2) to first focus on the functionality of the time series tools. 

I've attached an example workflow to show what a batch macro can do for this process. Check out the attached workflow and let me know what you think. 

*note: you'll notice that not all categories forecast all 12 months of 2022, that's because there's missing data in the 2018-2021 input for those categories. Just FYI if you notice that in the output. 

[Rhitik_Krishnani]

Hello charlie,

I am unable to see the accuracy % of the model

Can you help me with that

~Thank you for helping me through the alteryx journey

[CharlieS]

Ah, the batch macro that I built was focused on how to train a model and return forecasts for each category. 

If you are interested in testing models for each one, a similar macro could be built for the TS Compare tool to output results for each category. I made another example that run the TS Compare tool on each category.

If you open up that macro, you can change the model output to produce the model results you'd like to see. Check it out and let us know what you think. 

[Rhitik_Krishnani]

Hello @CharlieS 

Can we add forecast_ETS values also beside Forecast_Arima in the batch macros container part?

Attaching the flow for the same.

I request you to attach the final flow for the same.

We will close this discussion after this doubt

~Thanks

[Rhitik_Krishnani]

Please find attached flow

[Rhitik_Krishnani]

Hey @CharlieS 

Just for the better understanding of the desired output

This is the desired output columns:

Year Date Subcategory Forecast_Arima Forecast_ETS 

(Just Forecast ETS should be added)

Thank you for the help.

This is the last query then we will close down the discussion.

~Thank You

Rhitik Krishnani