Optimization with group constraints

[john-colarusso]

Hi all,

I have an interesting optimization problem I could use help with. I've attached a packaged workflow

Context:

We have various levels of demand across 11 states

We have a roster of suppliers who are licensed in various states and can provide various amounts of supply. A supplier cannot serve demand in a state in which they are not licensed

Our current method of allocating multi-licensed suppliers is to allocate supply proportionally to the demand in the licensed states (e.g., if a provider with 10 supply units is licensed in CT and AL, and CT has 50 demanded units and AL has 100, we would allocate 3.33 units to CT and 6.66 to AL)

Problem:

Overall, we have 790 demanded units and 950 supplies units - at the aggregate level we can serve all demand. However, some states are over-supplied and some are under-supplied relative to demand. How can I minimize the number and/or magnitude of under-supplied states?

From my understanding of the Alteryx Optimization tool, I need to set the problem up with an algebraic expression before feeding it into the tool. However, I'm struggling to do that for this situation. Any guidance or examples to get me started would be greatly appreciated!

Note: I know how to set the problem up in Excel (maximizing a cell that is the result of calculations and varying allocation percentages), but our actual dataset is too large to run in Excel

[RolandSchubert]

Hi @john-colarusso ,

my approach would be to create variables based on the combination of state and supplier (e.g. AL - 19) and create constraints based on demand of state (total of all state-supplier combinations for a state must be  <= demand of state  and total of all state-supplier combinations for a supplier must be <= total supply). Optimization function would be to achieve maximum of demand .

I've attached the workflow, but I'd guess the approach needs some "refinement", but it could give an idea. What do you think?

Best,

Roland