cancel
Showing results for
Did you mean:

# Alteryx designer Discussions

###### #SANTALYTICS

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

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!

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

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