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

Distribute workload to meet the target for multiple criteria for each staff

Joon
7 - Meteor

Hi

 

Looking for some help as I am stuck trying to solve this allocation problem. Have tried to search but can't seem to find a solution (with the introduction of the 4th criteria).

 

I am trying to allocate out a list of cases (~100 - 200) such that the sum of the load for each staff, for each criteria is as close to the respective targets as possible.

 

The 4 criteria are (1) Sub Task, (2) Complex Lvl 1, (3) Complex Lvl 2, (4) Checks Required. All are equally important, but if really need to prioritize, the total assigned value for criteria 1, 2, 3 should be as close to target as possible (deviation of 1-2 is still ok). Complexity level determines bulk of the effort.  It may not be possible to distribute evenly for criteria 4 as is not uncommon to see a wide range of numbers (e.g. a couple of case with 200 or 400 checks, while the rest as below 50). Nonetheless, other than 1-2 staff bearing the additional load, the rest of staff of the staff should have similar load. 

 

All cases must be assigned out.

 

Table 1: Data

Objective : Populate Assign Staff column

Case No.Sub Task Complex Lvl 1Complex Lvl 2Checks RequiredAssign Staff
11108A
21109 
3101150A
41015 
510112 
61012 
711018 
822012 
911019 
1021188B
1110141 
121106A
1322027B
141012A
151014 

 

 

Table 2: Staff list and their respective targets for each criteria

Sum of all the cases assigned to a staff

  Sub Task CountComplex Lvl 1Complex Lvl 2Checks Required
StaffAvailTargetAssignedTargetAssignedTargetAssignedTargetAssigned
A1543232116166
B1543331116115
C15030301160
D0.5302010580

 

So in the above assignment so far, B's portfolio is ok, as his assigned score is close to target.

 

Really appreciate any help, suggestions.

8 REPLIES 8
AngelosPachis
16 - Nebula

Hi @Joon ,

 

I've read your post and I can't stop thinking of this solution. Is your ultimate goal to maximise the total number of cases per employee (and then eventually assign all cases?)

Joon
7 - Meteor

Hi AngelosPachis

 

Not quite, I think (especially when it comes to criteria 4). The emphasis from the team is to make it equitable as far as possible.

 

I try to illustrate with a sample (manual) assignment (see attached .xls)

 

So far, this set of assignment look somewhat accept as criteria 1, 2, 3 are very close to target. But the no. of checks required assigned to B and C are not even (difference of 30), even though their targets are the same. So the team will take the list and reshuffle as again in an attempt to make it as equitable as possible for B, C (and maybe even D).

 

Hope the above makes sense and answer your question.

AngelosPachis
16 - Nebula

Hi @Joon ,

 

Understood, it sounds like a problem that can be solved with an iterative macro. However, I would suggest you have a look in a tool called "optimization" tool.

 

What this tool will allow you to do, is maximize the number of cases assigned to each member of staff, given certain constraints. In our instance, those constraints are Sub-Task count, Complex Lvl1 and Complex Lvl 2 (Criteria 4 is optional, so we can include it as is, exclude it totally or use a slightly increased value of the max permissible value it can take).

 

How this tool works :

 

First of all, you have to define the equation that you need to maximize; Probably that's the number of cases, because nobody said no to some extra work, right? So assuming that case 1 is denoted as c1 and case 2 as c2 and so on, the equation we want to maximize for each member of staff is:

 

c1+c2+c3+c3+c4+....+c15 = Total number of cases assigned to Staff A ----> Max

 

This equation above is called the objective function. You will notice that it contains all cases (c1 to c15) because essentially, we can assign any of them to staff A. Then, when a case is assigned to staff A, that will take the value of 1, else if it is not assigned, it will take a value of 0. So our variables (c1, c2 ...) in the objective function can only take values of either 0 or 1, so they are of a binary type.

 

For example, assuming that staff member A is assigned 3 cases in total (cases 1, 3 and 5) then the objective function will become:

 

1+0+1+0+1+0+(zero for all other cases) = 3.

 

All the different variables, their weight to the objective function and their type (binary) shall be fed from the "O" input anchor, so for staff A

 

AngelosPachis_0-1610787822205.png

 

Now assigning each case to an employee, comes with some constraints; each case have a SubTask, ComplexLvl1 and Complex Lvl2 weights, that when added up for each case, should not exceed the maximum allowable values for each employee.

 

So, assuming that SubTask is (S), the constrain equation for SubTask would be:

 

S1c1+S2c2+S3c3+S4c4+.....+S15c15 <= 5 (for employee A, subtask should not exceed 5)

 

Again, when a case is assigned to employee A, the variable for case (c1,c2,c3...) will take the value of 1 else the value of 0. The weights for SubTask for each case (S1,S2,S3,...)  have been provided from you and should be fed in the optimization tool through the A input anchor, in the following format

 

Untitled.png

 

So following the example given earlier, if cases 1, 3 and 5 are selected for employee A, then the SubTask constraint equation will become:

 

1*1 + 1*0 + 1*1+1*0 +1*1+1*0 +..... +1*0 = 3 < =5 ✔

 

So this constraint is met and probably we can fit some more cases for employee A. The same process goes for ComplexLvl1 and ComplexLvl2,  the general equations of which will be the following for employee A

 

CO1c1+CO2c2+CO3c3+CO4c4+.....+CO15c15 <= 3

 

CT1c1+CT2c2+CT3c3+CT4c4+.....+CT15c15 <= 3

 

If you would like, you can add an extra constrain for Checks required, which for employee A should not exceed 116.

 

The final step is to provide the maximum values to the model for employee member A, and those should be fed through input anchor B

 

Screenshot 2021-01-16 092215.jpg

 

The model will run and out of the "S" output anchor, you will get which cases should be picked for employee A, to maximise the number of cases assigned to him given the constraints we have defined above. So now, employee A can be assigned with cases c2, c5, c9, c11 and c12 before one of the constraints has been met. That's a total of 5 cases, and hence the objective value is 5.

 

Screenshot 2021-01-16 092545.jpg

 

If you need more info, those will come out of the D output anchor. Let's see how that looks

 

Untitled.png

Focusing on the Important constraints part, we can see that given the cases assigned to employee A :

 

SubTask = 5, which is less than or equal to the max permissible value of 5

ComplexLvl1 = 3, which is less than or equal to the max permissible value of 3

ComplexLvl2 = 2, which is less than or equal to the max permissible value of 3, with an extra capacity of 1

ChecksRequired = 87, which is less than or equal to the max permissible value of 116, with an extra capacity of 29.

 

That last constraint as you said is not as important, so you can go on an increase the max permissible value or remove it totally if you like.

 

Now moving onwards to employee B, you can manually remove variables and constraints for cases c2, c5, c9, c11, c12 and re-run the tool. This can be automated with a macro, but it's good to have control over the whole process before automating.

 

Thought this way would be more efficient than trial and error methods in excel, plus the fundamentals of this method lie in mathematical optimization.

 

This has been by far my longest post in the community, so please take the time to read it and come back if you have any questions. You can find the workflow I used as an example attached.

 

Regards,

 

Angelos

Joon
7 - Meteor

Hi Angelos

 

Really appreciate you the taking the time to write this fantastic post. I browsed the optimization tool previously, but was at a loss on how to set-up for my scenario. So far, I am able to follow your post which is clear and informative.

 

In the midst of testing it out. Will likely come back with a couple of clarification questions.

 

Regards

Joon

Joon
7 - Meteor

Hi Angelos 

 

I'm not quite sure how to go about modifying some of the constraints.

 

1) How to enter the constraints if it is a range, with a lower and upper bound? (E.g. Target for Sub task is 5, but the team accept results between 4-6. The same goes for the complexity constraints.)

 

I tried to enter in the following in the table to anchor B for Checks required, but encounter error msg

 

Joon_0-1611059317126.png

 

Joon_1-1611059342688.png

 

2) In a typical work list, it will always contain 1 or 2 outlier cases (i.e. where Checks required for the case runs into 200-500). I try to assign these out first. Currently I find the (lowest) max permissible limit for Check Required via trial and error. Is it possible to set-up to find the combination that meet the constraints for Sub Task, Complex Lvl 1, Complex Lvl 2 and also with the lowest no. of Checks Required and must include the outlier (c3 in this case)?

 

Appreciate your advice.

 

Thanks and regards

Joon

AngelosPachis
16 - Nebula

Hi @Joon ,

 

Regarding Q1, the constrain limits you add in input anchor B should have a 1:1 relation with the constraint equations as those are defined in input anchor A.

 

If you want to add another constraint, you can do that by fist editing the input to anchor A so it includes the correct coefficients for that new constraint equation. Since that new equation also refers to Checks Required, the coefficients will be the same to the original equation (just the field name should change) 

 

AngelosPachis_0-1611078320029.png

 

Then you can go on into the B input anchor and add the new constraint for the second equation.

 

AngelosPachis_1-1611078360370.png

 

Concerning Q2, I'm not quite sure I fully understand the question. Do you want to try and the optimization tool to assign the case with the highest checks required first? If yes, how are you inputting the constraints for each member of staff? If you are doing it manually, then that shouldn't be a problem. If I didn't answer Q2 then please feel free to rephrase it and hopefully I can help. 

 

If you provide an example, that always helps the reader understand the requirements better.

 

Regards,

 

Angelos

 

Joon
7 - Meteor

Thanks Angelos! Your advice for Q1 works. Now I can better refine the constraints for various staff and at the same time help solve my (not well articulated) issue in Q2 🙂 

 

AngelosPachis
16 - Nebula

Glad I was able to help @Joon ,

 

Reach out if you have any further questions and I will try to be of more help. Great question this one, thanks for bringing it up in the community.

 

Cheers,

 

Angelos

Labels