How to create datatset for a Time Series Model

Dear Alteryx Users,

I am working on a Time Series Model for a Retail Store-Product level data with the objective of predicting Monthly Sales at a Retail Store- Product level. For making predictions for Monthly Sales at Retail Store Product Level, I am using the Monthly Historical data available for Retail Stores for various products.

While working on this model, I was encountering issues with the ARIMA and ETS models that I am using and when I discussed the issues with Alteryx Support, I was informed that the issues are due to Nulls for Sales for few combinations of Retail Stores and Products and also for a few Retail Stores-Product combinations, the historical data for a few months was missing.

In order to resolve the issues with the two Time Series models, I am thinking of introducing dummy records for Retail Store Product level data for historical months and I have been exploring the Generate Rows tool available under the "Data Preparation" category of tools to handle this issue. However I am not able to figure out how to use the "Generate Rows" tool for multiple Years for eg. 2015 and 2016 .

Can anyone please provide some guidance of how to use either the "Generate Rows" tool to create historical data for Retail Store-Product Level combination or some other approach to enable the creation of the records?

Help

Accepted answers

BenMoss

Firstly, see my previous post regards why the issues seem to be occuring. Alteryx automatically writes the messages you get to a 'log file'. You can find the location of the log files Alteryx generates by going to options > user settings > edit user settings. Note you can quickly filter to just the 'ERROR' messages by going to by selecting the red error indicator on the toolbar of the results window.

As for your 2nd question, I would almost expect there to be differences. The ARIMA and ETS whilst both time series models, have some key differences in how they predict your result data and it's worth reading up on the subject to ensure your analysis captures what you need.

It is best practice to create a hold out sample of data (lets say, the last 3 months for each store-product for instance). You can then generate a forecast for this period for both your models and then compare against your actuals. By performing some basic calculations you will then be able to derive the best model to implement.

I hope this helps.

Ben

All comments

BenMoss

Here's how I'd approach the problem.

Use a summerize tool to group by your stores and products.

Do a second group by of your data to summerize by just your data field. Use the 'TS Filler' tool to fill in any missing dates that may be in your data stream.

Append these two streams together. This will give you a list of every single store, product and date combination that should exist in your data.

Finally, join this to your original data stream. If data falls out of the left side, or right side, depending on the configuration, then it indicates that a row does not exist for this combination. I would immediately union this. You will now have null values. I would then use the impute tool to replace my null values with zeros (though there are a number of tools you could use here).

Example attached.

You could also be a bit smarter with how you impute values, you could use the average for example.

Ben

AllCombos.yxzp

AshishBhavnani

@BenMoss

Thanks for your reply to the question I had posted.

I downloaded the package you had attached with your reply. However, I am not able to open the package since I am using an older version of Alteryx.

Is it possible for you to post a 11.0 x64 version of this Alteryx package or if you could post a snapshot of the Alteryx workflow, it would be really helpful too.

Thanks very much for taking out time for replying to this post. It has given me a good starting point to the problem.

Regards,

Ashish Bhavnani

BenMoss

I'll share a neat trick with you on how to downgrade .yxzp files for you to try, if that doesn't work then I'll give it a go myself.

1) change the .yxzp suffix to be .zip

2) unzip the folder

3) in the unzip folder locate the workflow and open in a text editor

4) on line 2, the version number is declared. Change this to 11.0

5) save

6) open with Alteryx

Ben

AshishBhavnani

Hi @BenMoss,

Thanks very much for sharing this trick. I was able to open the workflow in my Alteryx Designer by following the steps you outlined in your previous post.

Also, once I changed the Alteryx Document version for the Macros inserted in the workflow, I was able to open the Workflow without any warning messages popping up on my screen.

I would like to request your inputs on another issue that I am facing which is as follows -

After following the steps you had outlined in your previous posts, I was able to create records for missing Year-Month combinations for Retail Store-Product Level data.

However, once I feed this data to a Time Series Model which is running the data through both ARIMA and ETS Time Series Model, I am receiving an error message for certain records. The error message snapshot is attached to this post in a file labeled "Time Series Error Message"

The workflow that I am running to predict the Sales for a Retail Store-Product combination for Future Year-Months is relying on a Batch Macro which is iterating over Retail Store-Product combinations. This batch Macro has been inserted into the workflow and the control input of the Batch Macro has been fed with Retail Store-Product combinations data.The other input of the Batch Macro has been fed with the Historical Retail Store-Product level data which has data for Year-Month combinations that were missing from the original data.

Based on the above description, I have the following two questions -

Which of the two inputs contain the record number against which this error message appeared?

What could be the possible causes of this error message?

Time Series Error Message.PNG

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335