This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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?
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).
You could also be a bit smarter with how you impute values, you could use the average for example.
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?
Okay i've just picked this up and have it running, it seems to take a while to process mind!
What we need to do is isolate the instance which is triggering the error. In order to do this, in your outer workflow, select the canvas and go to the configuration pane, then hit run time. In the options at the bottom there is the option to 'view all macro messages', this should allow us to isolate the iteration when the issue occurs and thus the problem 'batch'.
I appear to have isolated the issue to batches where the sales value for a store-product combination is always the same, in every case in your data that value is likely always 0. But the error still generates if the value is always 1 or always 2, etc.
I guess you could filter those instances out of the batches and just predict the value '0' for the new periods.
You have 81 instances where this is the case (see below).