community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Optimization with Dynamic Constraints

Highlighted
Atom

Hello Team

 

I need a workflow to implement the following solution in Alteryx. The problem is as follows:

 

  1. I have a list of Designations (Column B)  , the number of people who work under those designations, and the total number of assignments to be completed by each
    1. For 2016 – Number of people in Column C and Number of Assignments Worked in Column D
    2. For 2017 – Number of people forecasted in column F and Number of Assignments Worked (Optimized numbers needed for this) in Column G
  2. Total Assignments – Product of Number of people in a designation x Assignments worked
    1. For 2016 – Column E
    2. For 2017 – Column I
    3. New Values for 2016 – Column H, these represent the Number of people in a designation x New Assignments to be worked (Column G)
  3. Bounds- These represent the Upper and Lower limit of Assignments to be worked for each designation
    1. Upper Bound – Column J, represents 10% increase on Column D
    2. Lower Bound – Column K, represents 10% decrease on Column D

 

Optimization Problem:

 

To be Maximized: The Total Assignments to be worked in 2017 (Cell I10)

 

Constraints:

  1. Sum of Total Assignments in 2016 (Cell E10) = Total Assignments in 2016 based on new work Assignments (Cell H10)
  2. The new optimized number of Assignments in Column G should be Between Corresponding Upper and Lower Bounds
  3. New Assignments optimized in Column G should be used to The Total Assignments to be worked in 2017

 

I have listed the problem in Excel as well and solved it using the ‘Solver’ functionality in Excel which helps to maximize or minimize a certain value based on certain constraints.


Issue we encountered while working on Alteryx : As Cell I10 has to be maximized and is based on optimization of Column G, hence ‘How to add this as a dynamic constraint’ in Alteryx.

 

Please help us create the same. Your help will be highly appreciated.  

Alteryx
Alteryx

Hi @Ambynith,

 

This hasn't been touched in a few days and it looks interesting so I had a crack at it in front of the tv tonight...

The first thing to look at here would be the optimization tool from the Prescriptive Toolbox, however that would require a rework of the question as one of your conditions is a sum of multiple rows. This may be possible with a re-structure of the data but I don't know that tool well enough to restructure the data quickly. There is an example that might lead you down the right path under 'Help > Prescriptive > 1_Optimization_Model_Input_Modes' (Optimization with Matrix Mode).

 

If you just want to solve your problem as in the excel sheet then we can construct every possible combination of ID's as it's not much data.

 

In the attached:

The Orange Box constructs the data

The Green Box runs every combination (This involves a poorly written iterative macro, I'm just not going to spend more time right now tidying it up, probably a silly mistake producing the extra 30k rows)

The Blue Box gets that back to a format we can summarize easily

The Purple Box finds the answer we're after

 

Parts of this could be re-used for similar problems, but I'm not sure how it could be generalised as I don't know all the things people 'solve' using Excel Solver. I'm sure there is probably better ways to construct and transform the data, but I tried to leave it exposed so it can be followed.

 

On the plus side, this solution also includes the conditions with bounds not met in Excel Solver.

 

Kane

Labels