Need Help Adding a row into my data Set Based on a Calculation/Logic

[MitchS]

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

[Kottapally_Ramesh]

Hi @MitchSewhoy 

Please see the attached workflow which will give you Projected Closing Stock & if you need to rollout the data to next month add a filter & select only "Projected Closing Stock" row to get the result.

Hope this helps.

[grazitti_sapna]

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!

[MitchS]

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. 

[grazitti_sapna]

Hi @MitchS, please take look at the workflow attached. I hope this resolves your query.

Thanks!

[MitchS]

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. 

[grazitti_sapna · Accepted solution]

Hi @MitchS, is this what you are looking for?

I hope this helps!

Thanks!

[MitchS]

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. 

[grazitti_sapna]

@MitchS, glad it worked for you.

Thanks!