Cumulative costs based on hierarchy

[Preshan]

Hi Guys,

Can someone please help me with the logic of how I can create a workflow that basically creates a accumulative cost based on how products have moved from Their respective shop types. Starting from Manufacturing to Distributor and then to the shop. 

The Hierarchy column shows how the products move from one shop type to another. Please note I used an example and in the full data set there are more products. The Hierarchy is something I just created to show how the products move and is not in the dataset. A for Manufacturing, B for Distributor and C for Shop.

I basically need to create a workflow that will group the send and receive thus creating a running total like below.

The red text is the returns of an item (B_A).

Thanks guys any advice will be greatly appreciated.

[DeanWest · Accepted solution]

Hopefully, this helps! I transposed the data and then used a Tile Tool to group the "Send" and "Rec" values.  After that, I used a Multi-Row Formula Tool to calculate the running total.

If you need any more assistance, please feel free to reply to my comment. 

Note: You can use the RecordID that is deselected in the last select tool to keep track of the original product order and record count of the incoming dataset. (see snippets below)

Grouped by Group_Number:

Sorted by Group ID

Grouped by RecordID:

Sorted by Original RecordID

Please mark this answer if it was an appropriate solution! 🙂

[Preshan]

Hey Dean,

Thank you very much. Excellent solution. 

Regards

Preshan

[Preshan]

Hi Dean,

Is it possible to subtract the return from the running total.

Example: The column running total should be able to show the subtracted amount from returned group.

In your solution you add the returns. 

Regards

Preshan

[DeanWest]

You're very welcome. I'm glad it was helpful! 

[DeanWest]

Hey Preshan,

As per your request, here is a corrected version of the workflow.

Please let me know if you have any questions or need any more assistance.

Here is a snippet of the corrected dataset:

Also, I added a snippet to highlight the change to the workflow. If you need to adjust the Running Total calculation in the future, feel free to change the formula in the Multi-Row Formula Tool. 

Take care,

Dean West

[Preshan]

Hi Dean,

Thank you very much for your quick response. 

Just to note I need the cumulative total to add when its moving like A_A, A_B, B_C but then I also want it to subtract when its returning like B_A.

Is there a way to show both rules being applied to one column ? 

Also the order of the shop type must be sorted. Starting from Manufacturing to Distributor and then Shop.

Thanks once again.

Regards

Preshan