Optimization of output in Alteryx (excel solver equivalent)

Question

Hello!

I'm running a set of simulations within a workflow, these take a set of structures as input and I'm looking to maximise the output.

I've created some scenarios manually (seen in the right join data sample above) and done a cartesian join to do each calculation to apply on my simulation. The output looks like this:

My objective is to maximise the return field in the output and I'm looking for a tool that keeps trying different combinations of inputs (i.e. increasing/decreasing limits, percentages and so on on multiple variables) till it reaches a maxima. These will need to be subject to some form of constraints so I can allow for realistic scenarios only.

I see the optimisation tool within prescriptive analytics is meant to do something similar but it seems like you need to define your constraints and functions within the tool itself. For me this isn't possible given the container 98 above includes some calculations I can't embed easily, any alternatives similar to excel solver that may work in this situation?

Thanks, R

dreldrel · Answer

Thanks for sharing @JAKupchik !

JAKupchik · Answer

Greetings!

I like the Weekly Challenge #213, but for internal uses, I made a simpler template workflow that emulates the Solver tool.  It concentrates on optimizing one field, a list of amounts, looking for a valid combination of addends for a target amount.  It is rather lean and can be a weekly challenge in itself.

Please enjoy!
John

Optimization Template - Solver Tool.yxmd

BrandonB · Answer

The optimization tool is going to be similar to an "Excel Solver" in that you optimize for a value and have constraints entered. Weekly Challenge #213 goes through a fairly straightforward example if you want to see how it is done in practice https://community.alteryx.com/t5/Weekly-Challenge/Challenge-213-Optimized-Flower-Arrangements/td-p/561687

We also have a Tool Mastery article for the Optimization tool which is very thorough regarding configurations: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Optimization/ta-p/306117

It sounds like the calculations in container 98 may be complex, so it could be a matter of just getting the data formatted into a structure that plays nicely with the constraints you can establish in the Optimization tool.