Finding amounts that sum to zero

jannis2021

Hi.

I have several data sets where I would like to build a workflow that identifies amounts that SUM to zero and segregate them from amounts that do not SUM to zero. Prior to Alteryx I would use Excel's solver functionality to accomplish such tasks. Is there a way to replicate this functionality in Alteryx? I attempted to use the Optimization tool, but am not familiar enough with the syntax to build out the parameters correctly. Can anyone assist?

Example data:

694,494.34	1
(77,749.62)	1
207,256.03	1
(1,517,382.72)	1
615,632.35	1
77,749.62	1
100,000.00	0

Optimization

Data Investigation

Iterative Macro

Accepted answers

jannis2021

I actually came up with the solution after posting this. Happy to post the workflow if anyone else is interested, thanks.

All comments

AngelosPachis

Hi @jannis2021 ,

After removing the brackets from the negative values, adding a minus sign at the front and converting that field to a numeric data type, you can make use of a Running total tool to see when the amounts add up to zero and flag it with a multi-row formula tool

Best,

Angelos

example.yxmd

atcodedog05

Hi @jannis2021

It is possible to share the excel with the solver present.

jannis2021

Thanks, that works well in my simplified scenario, but matched against a bigger dataset I think this falls apart? If not let me know. I attached a more complete data set for reference.

Alteryx Example.xlsx

jannis2021

I actually came up with the solution after posting this. Happy to post the workflow if anyone else is interested, thanks.

atcodedog05

Hi @jannis2021

Yes please, This is an interesting usecase and I would love to see how you solved it 🙂

EstebanDaMan

Hi @jannis2021,

Can you post the workflow with the Optimization tool. Thank you.

EstebanDaMan

Hi Folks,

I was able to create the workflow based on @jannis2021 last post. All credits to @jannis2021 for this wonderful solution.

Get Amounts with Zero Sum.JPG

Get Amounts with Zero Sum.yxmd

srikar235

Hi @estebandaman and @jannis2021.

Is there is any way to do this for different groups of data(based on a key column) in a single dataset.

For Example my data looks like

Key	Amount
1	3
1	4
1	2
1	10
1	-9
2	4
2	5
2	-9
2	12

And my desired output is

Key	Amount
1	10
2	12

Thank you.

OllieClarke

Hi @srikar235

I think in your example you just want to sum the Amount column, grouped by the Key column

Sorry My input is

Hi @srikar235

Here's a solution based on @jannis2021's previous work

srikar_2.yxmd

BS_THE_ANALYST

@OllieClarke just used this optimisation solution for another problem! Thanks for this 👌

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167