Optimization tool configuration help

Malsadeh

I've attached the dataset, which includes the warehouse name along with vendor pricing in columns I, J, K, and L.

My objective is to determine the lowest total cost while meeting the constraints (in pounds) provided.

The Alteryx workflow is also attached for reference.

I'm only allowed to use one vendor per warehouse.

Optimization tool utilization.yxmd

Optimization

Accepted answers

AkimasaKajitani

Hi @Malsadeh

The important point to use the Optimization tool is to create the constraints formula. You have to add all the constraints to the constraints formula.

The most difficult constraint of your constraints is that I'm only allowed to use one vendor per warehouse.

Normally the type is "C" because you want to decide the pounds. But I could't add the constraint "one vendor per warehouse". So, instead of using type "C", I decided to use type "B". And then I choose cost x primary lbs as coefficient. By doing so, I can add the constraint "one vendor per warehouse". That constraint formula is AI+AJ+AK+AL=1.

For A input:

For B input:

For O input:

Answer :

If you can allow multi vendor for the warehouse, the result is as follows.

@Qiu fun problem

Optimization tool utilization_AK.yxzp

Qiu

@AkimasaKajitani thank you very much. Let's invite another two Japan ACE to the conversation, 😄

@DaisukeTsuchiya @Yoshiro_Fujimori

All comments

alexnajm

I'm admittedly not as comfortable with this tool so it will take me more time compared to someone else perhaps, but I might take a look here to help start setting it up: Tool Mastery | Optimization - Alteryx Community

Qiu

@Malsadeh
I studied the link given by @alexnajm and from there I found this one is more helpful.
https://knowledge.alteryx.com/index/s/article/Optimization-Tool-Entering-a-Model-Manually-1583460627843
I can now only work with Manually input and somehow, my flow will work for upto 4 wareshouses and will fail for 5 and above.
I am wondering if there is any limitation on the number of variables?
@gawa @AkimasaKajitani
It is time for you to save us here. 😁

Optimization tool utilization-r1.yxzp

Optimization tool utilization-r10.yxzp

caltang

I'm not good with the tool either so this is helpful! Thanks @Qiu @alexnajm

Gawa

@Qiu

As I'm not good at the Optimization tool, when I face this kind of puzzles, I always start with brute-force approach. In this case, we have 8 warehouses x 4 vendors;4^8=65,536 patterns shall be examined and it's still easily doable scale with Alteryx to calculate.

- Create all patterns of selection of vendors for each warehouse.

- Check all patterns if constraints are met

- Find the minimum cost among the above met patterns

@Malsadeh

Just a side note. In this kind of problem, there can be another constraint that 'each vendor shall be selected at least one time'. Without considering this constraint, an optimized pattern is consisted of only vendor 'I' and 'J'.

I'm not sure this is the case for your problem, but I configured this logic into the attached workflow.

Optimization tool utilization_gawa.yxzp

Qiu

@gawa
Thanks for your hands out and the flow is super impressive. I definetely need some time to study it.

AkimasaKajitani

Hi @Malsadeh

The important point to use the Optimization tool is to create the constraints formula. You have to add all the constraints to the constraints formula.

The most difficult constraint of your constraints is that I'm only allowed to use one vendor per warehouse.

For A input:

For B input:

For O input:

Answer :

If you can allow multi vendor for the warehouse, the result is as follows.

@Qiu fun problem

Optimization tool utilization_AK.yxzp

Qiu

@AkimasaKajitani thank you very much. Let's invite another two Japan ACE to the conversation, 😄

@DaisukeTsuchiya @Yoshiro_Fujimori

Malsadeh

Hi Nebula,
Thank you for taking the time to share your workflow! It's the first one I've reviewed, and I’m genuinely impressed with how you approached and solved the problem.

What stands out the most is the dynamic design—you've built it in a way that allows additional warehouses to be added seamlessly. Great work!

The only take is that the total cost with that pattern is $19,772,076.15 (KLLILJJJ) which is higher than 19,761,076 (LJIKLKJK)

Malsadeh

Thanks for bringing the Intelligent people on the table!

Malsadeh

The Article is great Alex! Thanks for sharing.

Best,

Mahmoud

Malsadeh

Hi Akimasa Kajitani

That's exactly what I needed. Thank you for taking the time to solve this.

The total savings is the lowest out of all the combinations that I have attempted to find!

How can I make this dynamic? for example if I wanted to add a warehouse then I need to update the text inputs. Is that correct?

Best,

Mahmoud

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703