Optimizing a Function Given Parallel Corresponding Constraints

[Henry]

Hello,

So I'm still pretty new to using Alteryx on the predictive/optimization front and could use a little help with this problem. I am trying to build a workflow designed to optimize one value, lets call it revenue, while constrained by a second, lets call it cost.

Specifically, in my data set (An example one is attached here) I have ~150 "Areas" and each one has the option to select a revenue neutral option, and increase option, and a decrease option (Columns B-D). There are in turn corresponding costs associated with and dependent on each selection (Columns F-H). The goal is to maximize the sum of total revenue across all areas while costs are constrained at some given level (In this case say 0). 

I can kind of think of a brute force method that might work with a few area options but was hoping that someone might be able to provide a more elegant and efficient solution.

Thank You in advance!

[Joe_Mako · Accepted solution]

I followed the example for "Optimization with Manual Mode and Pre-Existing Data" from the Sample Workflow "1 Optimization Model Input Modes" in Prescriptive Analytics:

And made the attached:

- RecordID for joining and proper sorting of the columns when performing the cross tab later
- Arrange for reshaping
- Formula tools for adding in constants and renaming
- Cross tab for making the columns
- Dynamic Rename to rename them to match
- Join and Union tools to make the input matrices
- Sort, as done in the example
- Optimization configured to Maximize Objective and Dense matrix, variables in rows
- Join to bring it back to the arranged data
- Summarize tools to aggregate to different levels