Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Replicating Excel solver Maximization Problem in Alteryx

niteshsharma677
7 - Meteor

Hi Everyone,

 

I've been working on this problem for the last few days and I'm not able to crack this. I need to solve this anyhow. Can someone please help me or give me some direction?

 

I've shared an Excel document here. It has 14 Fields.

I will share some details here 

 

  • This is a Maximization Problem
  • We are maximizing cell M24 (Highlighted in Green) which is the sum of Final Quantities (M2:M23) (Highlighted in Blue)
  • We are Maximizing this by changing the Field 'First week Quantity' (Highlighted in Orange)
  • The first constraint is Field Final Quantities (M2:M23) should be less than Field Max Quantities (N2:N23)
  • The second constraint is Field First week Quantity should be integer
  • And the last constraint is Field First week Quantity should be greater than or equal to 0

And you can also see this in the Excel solver window

niteshsharma677_0-1668055941935.png

 

Apart from this few more details are below

  • YearWeek2 column is YearWeek1 + 6, YearWeek3 column is YearWeek2 + 6, YearWeek4 column is YearWeek3 + 6 .. and so on
  • Weekly Quantities of YearWeek2 are (Weekly Quantities of YearWeek1)*0.95, Weekly Quantities of YearWeek3 are (Weekly Quantities of YearWeek2)*0.9, Weekly Quantities of YearWeek4 are (Weekly Quantities of YearWeek3)*0.85 .. and so on
  • Final Quantities is the summation of all the Weekly Quantities corresponding to YearWeek1 (You can also check the Excel Formula for the same)

of course, I am able to complete this in excel but my task is to replicate this in Alteryx. I'm trying to use the optimization tool for this but I'm not getting the desired result. I'm not able to share the workflow that I've created as that is in my work computer and becuase of company policy, I am not able to share.

Can someone please help me?

Regards,

Nitesh

 

5 REPLIES 5
trung2403
8 - Asteroid

Hi, 

 

I think it's a bit tricky to solve this w Optimizer tool given that all Excel formulas will not work dynamically in Alteryx. Thus, I have to convert them to a flat formula before feeding it to Optimizer tool.

 

This is my answer:

trung2403_0-1668064613042.png

 

Setting up Variables for O anchor:

trung2403_1-1668064639447.png

 

Setting up constraint matrix for A anchor:

trung2403_2-1668064711800.png

 

Same like you, i'm using Company's laptop, thus unable to upload any file to here. But if you still need it, do let me know. I will upload when i get back home using my personal laptop.

trung2403_3-1668064843157.png

 

niteshsharma677
7 - Meteor

Hi @trung2403

 

Thanks a lot for your Answer. Yes, can you please upload your files? 

 

I was taking the below as my input for O. The coefficient that I was taking was totally the opposite. 

 

  • This might be stupid but can you please tell me how did you convert them into flat formulas? What I did was just made the column B (First-week Quantity) 1 and took the column M as coefficient

  • Secondly, can you tell me how can I dynamically create the constraint matrix from the Problem statement file
variablecoefficientlbtype
20220110I
20220210I
20220310I
20220410I
20220510I
20220610I
2022071.950I
2022081.950I
2022091.950I
2022101.950I
2022111.950I
2022121.950I
2022132.8050I
2022142.8050I
2022152.8050I
2022162.8050I
2022172.8050I
2022182.8050I
2022193.531750I
2022203.531750I
2022213.531750I
2022223.531750I

 

Thanks,

 

Nitesh Sharma

niteshsharma677
7 - Meteor

Hi trung2403

 

 

Thanks a lot for your Answer. Yes, can you please upload your files? 

 

I was taking the below as my input for O. The coefficient that I was taking was totally the opposite. 

 

  • This might be stupid but can you please tell me how did you convert them into flat formulas? What I did was just made the column B (First week Quantity) to 1 and took the column M as coefficient

  • Secondly, can you tell me how can I dynamically create the constraint matrix from the Problem statement file
variablecoefficientlbtype
20220110I
20220210I
20220310I
20220410I
20220510I
20220610I
2022071.950I
2022081.950I
2022091.950I
2022101.950I
2022111.950I
2022121.950I
2022132.8050I
2022142.8050I
2022152.8050I
2022162.8050I
2022172.8050I
2022182.8050I
2022193.531750I
2022203.531750I
2022213.531750I
2022223.531750I

 

Thanks,

 

Nitesh Sharma

niteshsharma677
7 - Meteor
trung2403
8 - Asteroid

Hi,

 

Sorry for my late response. I was out of town last weekend. This is how i convert the formula

trung2403_0-1668384892920.png

And this is how to set it up dynamically with Alteryx, you can change n to any number that you want as i have a summarise tool to find Max.

trung2403_1-1668384969187.png

For Constraint matrix, I will think on how to set it up dynamically and get back to you later. Currently, i set it up manually.

 

Best.

Labels