Arranging Projects Over a Year to Evenly Distribute Hours

[ColinMarc]

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!

[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! 

[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.

[clmc9601]

Hi @ColinMarc,

This is a simple fix. Open the workflow in Notepad and change the version number at the top to be compatible with the version you are using. Save the file, then you should be able to open it. In this case, I already did it for you. I hope it helps!

[ColinMarc]

I have changed a workflow version before but for some reason when I open this in notepad it has a bunch of text like

 =¨µl0×
€è²l0×
€è²l0×
í]ënÛ:þ_`ßAG,Ú=§±eçzVöA'­¹tc·¶ÅflJ¤AIIÜWÛûHû
Ë‹dQ©Èò¥N¢Mmj8œ!‡3ߐý¿ÿü×üãÑu´{€=Á–nlÕu À
ÚpÔÒ?÷Oßïë´ÿòÆ<t|€§4\ }múè¿ ÜÒõF}k['4šf^ !ðØÇð‹ÖGÈévg=,&>vø>iÂÓ>9ÁÈ&-‡ü,ð"Pm[ŒºpøÂǘáõ y¶Od×[úî®®M[zsg[×jqs5¡½¸ôF€}x"»cïìQ€-ÊSx@}öÀ©í &‚vo9
h駖ã
±1®¸å‚vÇò-ͦÄf$Hoo1¸OWìÑé œá¹5ÉoârB´œ|ª>xô™¼‰â\5¹{…¼ˆùnš1#b‚z™ò艉Ò-tóŸ`àw;2)Úr- ™j´_µG#h!· uÏ·°¯¹úc©Þ5…â&qs,)$Ńö®Y¨žç<»@÷À½8‡¤‘÷¬Þ0Þ×›ï놜ȬÉ$ÞP5¶_†;KS£YRã±…äPäq^¶MýL-–gR?S‹ù,ʬI¼—YË ‡5YpQ×0!D>+Õ:¶7q¬é9Á-½®'ùRŸžŽcpgŽ³ m·»<¸Ê&ꟁ»¼mÖâÊbñ9ð­.¼CÚ AHâ ƒH—ŸÂ „ò
RÚt*b¡‡< E³Nü]×üé„”M}IL“ÆIÏþ
(²ÊáÖó1=OðK†SÎua®‰¨»¾‰ˆ¬æD,8jÒ¡1kÑØ
0Q‚ÍH (˜¡Uþµã82ÀÊn

I am using Alteryx version 2020.1 I tried to open the most recent workflow you post and it is still giving me the following error... Thanks for the help!

[ColinMarc]

Hi @clmc9601 for some reason when I try to open the workflow in notepad it is a bunch of random characters. I have changed the version to a workflow before using that method so I'm not sure what is going on. I have Alteryx 2020.1, unfortunately the same error is happening with the workflow that you updated.

Thanks for any help you can provide!

[clmc9601 · Accepted solution]

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:

 (put "domain" in the questions tab too)

 

[ColinMarc]

Sorry for the late reply - this is awesome thank you so much! Do you have any recommendations on how I can learn more about macros this is really cool and interesting to me. I have some python and R knowledge so I don't think it will be too hard for me to learn.

Also, the data that I am actually using has 127,000 rows and it takes forever to run, any recommendations for speeding it up?

Once again thank you!!

[clmc9601]

Hi @ColinMarc,

Sure! Here is a link to Alteryx's interactive lessons on macros. Iterative macros are very similar to while loops in Python, but iterative macros are just the beginning. One tip that took me a long time to realize is that in Alteryx, an iterative macro will stop running when there are no records left flowing through it. So you just have to build it so that when your criteria are satisfied, all the records naturally exit the macro.

Regarding slow performance-- you can remove or disable browse tools, enable AMP engine, or use your new Alteryx macro skills in the near future to build a more efficient macro. The macro I built is creating all possible combinations and narrowing it down from there, so it gets slower exponentially with every additional record. There probably is a way to solve it without creating all possible combinations, but I didn't come up with it haha. If you're wanting to change parts of the workflow and re-run, you can use the "cache and run" option to save the macros from running again. This may or may not help your use case though.

I hope this helps!