Optimization tool - Objective based on an aggregate of data

Question

Hello Community,

I have a linear programming application that i need to reiterate for multiple objectives.

My goal is to minimize the sum of dist*shipments by manipulating the lat and lon columns. I am using excel solver to do this, but i have about 75 data series to do this with and am hoping to automate this into batches.

is there a way to set up the objective as a reference to a formula and set constraints up based on each summary?

the sum formula is summing up the dist*shipments column, and is the objective in the solver.

lat and lon are the constrained variables with

lat:  0 <= lat <= 50

lon: -150<= lon <= 0

Currently I am trying to use the optimization tool, but am not sure how to format the data to reach my goal as the objectives are static.

any input would be really appreciated.

thanks!

Manny

Alteryx Example.xlsx

OTrieger · Answer

@mannyr 
You can use Spatial tools, Trade area can solve this problem for you. I'm suggesting for you to do the interactive lessons on Spatial tools, around 20 minutes in total or so. That is exactly what is covered on these lessons. Do them all to get a better understanding.

mannyr · Answer

thank you for the reply!

My problem is that the objective is a calculation that references multiple rows.

In my example, the excel solver is looking at 207 rows of shipments.

these are my variables:
A:Customer latitude
B: Customer longitude

C: latitude
D:  longitude

distance = 69*SQRT((A-C)^2+(B-D)^2)

the objective is to minimize distance * Shipments by finding C and D

in other words, my coefficient in the optimizer needs to reference an aggregation of multiple row to minimize the objective.

I havent been able to find an example that does this.

I hope this information helps.

Regards,

Manny

OllieClarke · Answer

Hi @mannyr 
I think you should be able to do this with the optimisation tool. I'm not sure I followed your description of the problem though.

If you want help in configuring the tool, and the data setup it requires, I'd recommend going through the optimisation sample workflows which you can find in the help menu:

You can also check out these blogs on the tool:

https://community.alteryx.com/t5/Tool-Mastery/Tool-Mastery-Optimization/ta-p/306117

https://community.alteryx.com/t5/Alteryx-Success-Stories/Prescriptive-Analytics-Unleash-the-Optimization-Tool/ta-p/496332

https://medium.com/@robinvm/alteryx-predictive-master-part-2-the-power-of-optimization-4f467165fc6e

Hope that helps,

Ollie