community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

Gather all 9 clues to complete the final Weekly Challenge on Dec 16!

Learn More

Data Challenge - Predicting batch switchover

Asteroid

Hi All

 

I have been tasked to come up with a system to predict when we will begin to use a new batch of material and how much will be left over when we do.

This will have to be applied to a number of products but I am starting out with 1 to see if the system works.

 

I have the usage of the product in months, the stock and the number of batches:

Record IDMaterialBatchStock amountUse Mth 1Mth 2Mth 3Mth 4Mth 5Mth 6
1A1[Null] 232463
2A1EU5510      
3A1EU6620      

 

Ideally this is what I would like the output to look like:

Record IDMaterialBatchStock AmountUse Mth 1Mth 2Mth 3Mth 4Mth 5Mth 6
1A1[Null] 232463
2A1EU5510853-1  
3A1EU6620   16107

 

The usage per month is subtracted from the stock amount for the first batch, until it reaches a minus figure (which means there isn't enough) and then it switches to using the next batch and then carries on with that one.

 

There could be more than 2 batches and there will be multiple materials (identified by different material numbers) but I thought I would start out with the easiest scenario.

 

Hope its not too difficult!

Highlighted
Alteryx Certified Partner

Hi @G_SAND,

 

the amount of historical data you have will contribute to the accuracy of any prediction or forecast, but you could use ARIMA Time Series modelling to extrapolate out the predicted usage levels. This will then give you a specified number of months into the future and a predicted usage level for each month.

Once you have this, you can transpose the data into columns, and use a running total to determine the stock remaining for each item after each month. This will then allow you to create a formula to trigger the next in line stock once the stock of the previous has been depleted.

Let me know if you need help building this.

 

Asteroid

Hi @mceleavey

 

Thanks for the reply. We are already able to get a prediction of usage from our data which we can use to calculate the 'months coverage' of a particular batch which works quite well for some of our needs. But I was hoping to get something a bit more accurate.

 

I have taken a slightly different tack by splitting out the batches, first calculating the end point of batch one, then 'joining' that date in a separate table and trying to get that as the start date.

If I can get this to work then it also helps me include the expiry date of the material in the calculation.

 

Here's hoping!

 

Thanks

Labels