Identify groups of fields that sequentially sum to Zero

[Danny19093]

Hi guys, 

New to Alteryx but loving getting in and about the forum and learning from you guys.

I have a bit of a puzzling problem that I conceptually understand how to do mechanically but have no idea where to begin formulaically. 

I have a list of numbers in a column. I want to uniquely group the fields that sum to Zero and give them a unique group ID. There are multiple groups of fields that sum to zero. The entire column sums to 0.

I know this is computationally very hard for a computer but the benefit of this list structure is that the groups are sequential. See example table below what the ideal solution would be.

-50	Sum to 0 A
50	Sum to 0 A
-45	Sum to 0 B
-35	Sum to 0 B
60	Sum to 0 B
20	Sum to 0 B
15	Sum to 0 C
15	Sum to 0 C
15	Sum to 0 C
-45	Sum to 0 C

I can produce a solution that works for where there are two equal and opposite fields but my solution falls down where multiple fields that aren't equal and opposite = zero.

If it's possible to add stop conditions, would this reduce the computational power required. ie, once Alteryx has found a group that = zero, it will move on. 

I've attached the data is anyone wants to have a play.

Appreciate any suggestions!

Thanks

Dan

[SteveWayne · Accepted solution]

Hi @Danny19093 

You can do this with three key steps

- Running Total (assumes the data is in the right sort order for transactions summing to Zero - else you will need to sort data first)

- We then flag rows that have a running total of 0 (i have rounded to 4 decimal places to ensure it sums to exactly 0)

- Multi-row formula tool - which then changes the "Transaction Grouping" each time the rows sum to Zero, you can then create labels for each of these groups if required, but this will work on any number of transactions!

Hope this helps. Output and example workflow attached

Steve

[Danny19093]

Hi @SteveWayne,

Absolute legend, thank you very much for that, exactly what I was needing!

Thanks

Dan