Keeping inventory for each group item

Question

Hi all,

I have a problem I haven't managed to solve yet.

I have an inventory to keep track of for a given set of items. I found a way to get it using transpose and multi row formula but can't get Alteryx to differentiate between each item.

Here is an example of the table that I have to work with. I'm trying to get the 2 yellow columns. Inventory get opening + transactions column added cumulatively for each item code.

Closing is the position at then end when everything has been added.

ITEM CODETransactionsORDER DATEopeningInventoryClosingXXX10004/04/20220100200XXX15027/05/20220250200XXX5029/06/20220200200YYY20004/04/2022100012001100YYY-15004/05/2022100010501100YYY-5017/06/2022100010001100YYY10029/06/2022100011001100

Any help would be very appreciated.

Many thanks,

Nev

Nevi1211 · Answer

Hi Chris,

Thanks for your reply. I just edited my post with a table. I will try your solution now and let you know.

ChrisTX · Answer

Take a look at the Running Total tool.

Or you can use two Summarize tools, both with a Group by on Item Code. The first Summarize would capture the First value for Opening.  The second Summarize would calculate the Sum of Transactions.  Then Join the two Summarize results together and calculate Opening minus Inventory.

If you post your sample data as a table, not an image, it's easier to help mock up an example workflow.

Chris