Arranging Projects Over a Year to Evenly Distribute Hours

I am attempting to evenly distribute projects over a year so that the number of hours in each month are almost the same for each month. However, the projects are also assigned to a specific "domain" and can stretch over the course of multiple months. So, I am attempting to redistribute the projects over a year in a way that for each "domain" the sum of hours is as close to even as possible for each month. But if a project is originally from March to June (4 months) the project can be moved to August to November (4 months) but it can't be split up over non-sequential months.

I attached some sample data. I have looked into the tile tool but I'm not sure if it has all of the capabilities I am looking for.

Here is also an example of what my data currently looks like, you can see how the distribution is right-skewed.

Any help would be greatly appreciated!

EvenDistributionData.xlsx

Dynamic Processing

Preparation

Optimization

Iterative Macro

Prescriptive Analytics

Accepted answers

clmc9601

Hi @ColinMarc,

The window you showed a screenshot of isn't an error-- it's just a warning. You'll still want to open the window or click OK, and it should work!

If that doesn't work, I have attached the individual workflows. The problem with doing it individually is that you will have to configure all the macros and remap them to the right tools (they'll show up as question marks in the workflow).

Here are the configurations of the macros once you map them to the right filepath:

Screen Shot 2021-04-13 at 12.33.16 PM.png

(put "domain" in the questions tab too)

Screen Shot 2021-04-13 at 12.34.04 PM.png

EvenDistribution.yxmc

EvenDistribution_Final.yxmd

EvenDistribution_batch.yxmc

All comments

clmc9601

Hi @ColinMarc,

I built a workflow that attempts to help you out. However, what I ended up with might be too complicated to be practical-- up to you. The bulk of the workflow is within the batch macro (which takes each domain separately).

Basically the workflow uses macros to create all the possible starting months for the different projects, one domain at a time. Then, it compares the different combinations to see which combo is the best. How "best" is decided can change the results. I tried it with Root Mean Squared Error and Total Squared Difference, both actual from median of the month sums. This worked for Domain C, but since Domains A and B don't have the full range of months, it rewarded combinations that had the smoothest sums even if they didn't include all months. If you end up using this workflow and your actual data contains these range gaps, I'd highly recommend revisiting this metric.

I hope it helps!

EvenDistribution_Final.yxzp

ColinMarc

Hi - thank you so much for the help. Unfortunately Alteryx won't let me open the workflow because my version of Alteryx is older than the one you are using.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703