Optimization tool - Objective based on an aggregate of data

[mannyr]

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

[OTrieger]

@mannyr 
Yes you can do it with a Blob tool.
What you will need to do is create and empty excel template with all formula set up, normally what I'm doing taking existing excel file and deleting all the data in it. Here you can go in 2 ways, leave one row with the formulas then write in the data and after it copy the formulas to all relevant rows or estimate how many rows you will have and then set them in advance with all formulas in place.

What you will need to do is pull in all the data into Alteryx, bring the data to the designer layout for the output.

Then use Blob tool to call for the template, write it out and then write the data in to the specific range.

That will enable you writing the data into the formatted excel and get the desired result.

[OllieClarke]

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-Optimiza...

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

Hope that helps,

Ollie

[mannyr]

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 

[OTrieger]

@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.