Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

How to create datatset for a Time Series Model

AshishBhavnani
8 - Asteroid

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?

13 REPLIES 13
BenMoss
ACE Emeritus
ACE Emeritus

 

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

AshishBhavnani
8 - Asteroid

@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
ACE Emeritus
ACE Emeritus

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
8 - Asteroid

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?

 

 

 

 

 

 

BenMoss
ACE Emeritus
ACE Emeritus

I assume this is the 'TS Factory' tool, or along those lines?

 

If it is at all possible would you be able to share your workflow as a .yxzp so we can replicate the error on our machines and debug (options > Export workflow). 

 

If you cannot share the data could you perhaps try and replicate the issue with some sample data.

 

Ben

AshishBhavnani
8 - Asteroid

Hi @BenMoss

 

Please find attached with this post the exported package that comprises of the workflow and the associated sample datasets.

 

Thanks again for taking out time to look at the attachments.

 

Regards,

Ashish

AshishBhavnani
8 - Asteroid

Hi @BenMoss

 

Please find attached with this post the exported package that comprises of the workflow and the associated sample datasets.

 

Thanks again for taking out time to look at the attachments.

 

Regards,

Ashish

BenMoss
ACE Emeritus
ACE Emeritus

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'.

 

Ben

BenMoss
ACE Emeritus
ACE Emeritus

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).

 

Also attached trouble shooted workflow.

 

Ben

 

RetailStore-Product
Store104Product61
Store104Product75
Store109Product18
Store109Product45
Store109Product67
Store146Product105
Store146Product16
Store146Product20
Store146Product28
Store146Product33
Store146Product53
Store213Product31
Store213Product43
Store213Product52
Store217Product31
Store217Product43
Store217Product52
Store219Product31
Store219Product43
Store219Product52
Store258Product1
Store258Product7
Store258Product8
Store324Product104
Store350Product10
Store350Product11
Store350Product21
Store350Product27
Store350Product36
Store350Product48
Store350Product51
Store350Product72
Store362Product105
Store362Product13
Store362Product16
Store362Product19
Store362Product20
Store362Product28
Store362Product33
Store362Product53
Store362Product69
Store384Product105
Store384Product13
Store384Product16
Store384Product19
Store384Product20
Store384Product28
Store384Product33
Store384Product53
Store384Product69
Store385Product27
Store385Product29
Store385Product46
Store385Product48
Store385Product51
Store385Product55
Store385Product72
Store385Product76
Store385Product87
Store389Product105
Store389Product13
Store389Product14
Store389Product16
Store389Product18
Store389Product19
Store389Product20
Store389Product23
Store389Product33
Store389Product45
Store389Product67
Store389Product69
Store43Product100
Store43Product37
Store43Product39
Store43Product49
Store43Product66
Store46Product100
Store46Product37
Store46Product39
Store46Product49
Store46Product66

Labels