Hey Community,
I am just starting out using Alteryx and need some help with a problem I have.
I have the following columns in question:
Material Number | Category | Value | Date |
I need to be able to add a new row which is "Projected Closing Stock" for an individual material/ Month combo. The calculation for Closing Stock is = to Opening Stock on hand - Demand + Production. Where the opening stock on hand for all months (excluding August) is the closing stock from the month before.
I have added an example workflow with the desired result.
I would appreciate if someone could help in solving this :)
Solved! Go to Solution.
Hi @MitchS, I have a query related to your desired output.
For the month of June 2022, you need projected closing stock= 20 which will be achieved using
Closing Stock is = to Opening Stock on hand(closing stock of the previous month except for August) + Demand + Production
But for the month of July 2022 you need
Closing Stock is = to Opening Stock on hand(closing stock of the previous month except for August) - Demand + Production
Are you calculating the month of June 2022 and July 2022 separately? Can you please clarify?
Thanks!
Hey @Sapna Gupta
Sorry, Yes I would want June 22 Closing Stock to be calculated off the May 22 closing stock (June 22 Opening) - Demand (Sales) + Production and then have the same happen for July 22 but based on June 22 Closing Stock.
Forgive me for putting in bad example values which would give a Negative closing stock for June 22. For this, if we just change the May 22 closing stock number (which I have hard-coded but would want to be calculated as above) to say 20 and kept June 22 Demand as 10 and Production as 5. S Closing Stock for June 22 would then need to be 15.
Hopefully this helps,
Cheers,
Mitch.
Hi @MitchS, please take look at the workflow attached. I hope this resolves your query.
Thanks!
Hey Sapna,
Thanks for your help on that, I think I had given a bad example so I have given a bit more info and updated the input tool in the attached.
Essentially I have a fixed 24 Month range of Aug-21 till Jul-23. This will update in Aug 22 to be Aug 22 out till Jul 24.
At the end of Aug 21 there was an opening stock position for x number of SKU there was also demand and production for x number of materials (some may have just had production and no demand and vice versa). The calculation of closing stock for Aug 21 would be Opening stock -Demand + Production this then would sit in place as the opening stock for Sep 21 and the calculation based on demand and production would run again. I need to be able to scale this for my 24-month range over multiple SKUs and have it so it can easily be rolled once one financial year finishes.
I have given an example with multiple months and multiple SKUs.
Appreciate your time and help on this.
Thanks,
Mitch.
Hey Sapna,
I have made a few small tweaks to fit my data set but it is working as expected !! Thank you very much for the help!.
Cheers,
Mitch.