Column Aggregation based on the Previous Columns

I am trying to Aggregate the value in one column based on another. Please refer to the attached excel file for the example. I will try to explain here -

For example we have these columns:
DATE - Product - DESTINATION - Value
and we are trying to create a Consolidated Value

|01/06/22|WHSPR CHOICE WINGS 7|Goa|4.98| - Take this as it is (4.98)
|02/06/22|WHSPR CHOICE WINGS 7|Goa|5.32| - Add 4.98 + 5.32
|03/06/22|WHSPR CHOICE WINGS 7|Goa|3.32| - Add 4.98 + 5.32 + 3.32
|04/06/22|WHSPR CHOICE WINGS 7|Goa|5.32| - Add 4.98 + 5.32 + 3.32 + 5.32

Now for the next combination

|01/06/22|WHSPR CHOICE ULTRA 6S|HYD DC|0.94| - Now again this will be same (0.94)
|02/06/22|WHSPR CHOICE ULTRA 6S|HYD DC|0.96| - Add 0.94 + 0.96
|03/06/22|WHSPR CHOICE ULTRA 6S|HYD DC|0.95| - Add 0.94 + 0.96 + 0.95
|04/06/22|WHSPR CHOICE ULTRA 6S|HYD DC|0.98| - Add 0.94 + 0.96 + 0.95 + 0.98
|06/06/22|WHSPR CHOICE ULTRA 6S|HYD DC|0.99| - Add 0.94 + 0.96 + 0.95 + 0.99

Again for the next Combination
First value will be the same and the consecutive values will be added according to the date, Product and Destination.

Example File - example.xlsx (9.7 KB)

Hi @saurabhtranzita

Welcome to the forum!

A way to approach this is by using moving aggregation. Loop through each row for the three groups, get the value from the previous row and then get the moving aggregate it. Afterwards, add the original value to it. Optional apply some rounding.

See WF:
moving aggregation.knwf (41.9 KB)

PS: There is a small error in your Excel sheet for TIDE 3IN1 SEMI 1KG so the values are different :wink:

Hope it helps!

2 Likes

Thanks a lot. This worked like a charm.

2 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.