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

Budgeting Workflow Similar to Solver Functionality

Alexios81
5 - Atom

Hello,

I am wondering if it is possible to use a short Alteryx workflow as a budgeting aid. I have looked into using the Optimization tool for this, but have run into some difficulty since it seems to be intended to maximize values instead of solving for a particular budget. I have been able to use Excel's solver to find the answer to this problem, but I'd like to do this in Alteryx of course.

Here is my problem information. Imagine a concession stand that must stock certain items in certain ratios.


User inputs:

Cost per unit of each item type to be purchased (e.g. popcorn, soda, etc) -> C1, C2, C3

Percentage of units dedicated to each item type (should add up to 100%) -> P1, P2, P3

 

Budget -> B

Equation to Solve:
B  = (X1*C1)+(X2*C2)+(X3*C3)

Solve for X1, X2, X3

Xs are the number of units of each item to purchase


Constraints:

X1 = (X1+X2+X3)*P1
X2 = (X1+X2+X3)*P2

X3 = (X1+X2+X3)*P3

(so for example, if P1 was 30% then X1 would make up 30% of the total units purchased)

Any help or suggestion would be much appreciated. Sorry if my notation is unclear.

4 REPLIES 4
jdunkerley79
ACE Emeritus
ACE Emeritus

If I am understanding your question correctly then I think you can solve this fairly easily.

 

Let T = X1 + X2 + X3

Then X1 = T * P1, X2 = T * P2, etc

 

Given B = (X1 * C1) + (X2 * C2) + ...

That is equivalent to B = T * P1 * C1 + T * P2 * C2 * ...

Or B = T * (P1 * C1 + P2 * C2 .... )

 

Then re-arrange and get T = B / (P1 * C1 + P2 * C2 .... )

 

 

This can then be easily put into Alteryx like:

2018-10-24_19-49-31.png

 

This will allow for non integer quantities but otherwise I think should solve your problem. You could solve the integer part as a final rounding on the formula.

 

Sample attached,

Claje
14 - Magnetar

Correct me if I'm wrong (because I totally could be!) - this solution solves for how many dollars are spent on each item, but not how many of each item to buy.

This is relatively trivial to add, since you simply take the final calculation (QTY in the above example) and use a formula to create a new field with the following:

QTY/Cost

This still has the same rounding issue, but should accomplish the goal.

Alexios81
5 - Atom

Thank you for your response. 

I agree with your formulation, but I believe something is off about the workflow. I believe T is coming out wrong.

As I see it, under your inputs we should get T=100/([0.25*3.12]+[0.45*5.98]+[0.3*4.25])  or T = 21.07. Have I misread this? Certainly purchasing 118.65 bags of popcorn at $3.12 each does not suit a budget of $100.


jdunkerley79
ACE Emeritus
ACE Emeritus

Sorry yes there was a mistake in workflow

 

 

[Budget]*[Sum_Weighted Cost]/[Sum_Percentage]

instead of

 

[Budget]/([Sum_Weighted Cost]/[Sum_Percentage])

Corrected attached

Labels