Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Identify groups of fields that sequentially sum to Zero

Danny19093
5 - Atom

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
50Sum to 0 A
-45Sum to 0 B
-35Sum to 0 B
60Sum to 0 B
20Sum to 0 B

15

Sum to 0 C
15Sum to 0 C
15Sum to 0 C
-45Sum 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

2 REPLIES 2
SteveWayne
7 - Meteor

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
5 - Atom

Hi @SteveWayne,

 

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

 

Thanks

 

Dan

Labels