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

[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

[atcodedog05]

Hi @jannis2021 

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

[jannis2021]

Solver example attached. The fact that the perfect offsets were not picked up is not a big deal since I would strip those out of the population before attempting the Solver application.

[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.

[jannis2021 · Accepted solution]

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 🙂

[jannis2021]

I'll need a bit to tweak my workflow to be sufficiently generic, but the basics are below:

- Feed in a listing of numbers you are researching with Alteryx generated record IDs that are fed into the Optimizer as variables.

- Their coefficients are 1 (integer) with LB of 0 and UB of 1. These are binary variables.

- The constraints are set up to be the actual value of the number itself. Configure the optimization tool to be a  dense matrix with variables in rows.

- The objective to feed into input "B" is that the constraint field == 0.

- Toggle the Maximize objective to be true in the Optimizer tool.

This has been working effectively across multiple tests; keeping in mind that I am not tweaking the built in "Slack" in the tool so sometimes my numbers do not always exactly equal zero but this is acceptable in my use case.

[atcodedog05]

Hi @jannis2021 

Awesome 🙂 Thanks for sharing

Cheers and have a nice day!

[EstebanDaMan]

Hi @jannis2021,

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