community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More
We will be upgrading the Gallery to our 2019.4 release this Saturday, December 7th beginning at 9:00am MT. We expect the outage to take last approx. 2.5 hours.

Combination of amounts that equal a subtotal

Alteryx
Alteryx

Is there a relatively simplistic way to identify/select all records and their respective amounts to equal a given subtotal? I've included a pivot table below as a reference. For the first 'bucketed total', I'm trying to agree out only the amounts (highlighted in green) that agree to the total amounts (highlighted in gray). 

 

Thank you!

 

jonathanl14_1-1574101051293.png

Alteryx Certified Partner

Hi @jonathanl14 ,

 

Attached is my idea of how to accomplish exactly what you want. See if that helps you.

 

Best,

Fernando V.

Nebula
Nebula

Hi @jonathanl14 

 

For these types of of questions with oddly formatted input, it's always easier for us if you attach the input in a format, .yxdb, csv, .xlsx, so that we don't have to key in all the data.

 

And I believe the answer is no, There is no relatively simplistic way.  That's not to say it can't be done, but it's not simple.  There are 2 ways that come to mind.

 

1. Generate all possible combinations of your inputs and take that one that matches your Total. 

2. Formulate the problem in a way that the Optimization tool can solve it.  This would be something like 

 

Ax+By+Cz=Total,

 

where x, y, z are the rows in your input and A,B,C are constrained to be either 0 or 1.  Check out this post for a solved example of an optimization problem that has {0,1} constraints on the coefficients 

 

Dan

 

 

 

Labels