Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More
We will be upgrading the Gallery to our 2019.4 release this Saturday, December 7th beginning at 9:00am MT. We expect the outage to take last approx. 2.5 hours.

Data Challenge - Predicting batch switchover


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


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
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!

Alteryx Certified Partner



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.



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!