Binning Based on Multiple Columns

[Spresza]

I have a dataset that I need to assign date estimates to. Each record is a location (with a unique ID) that has a region and decimal number assigned to it.

I need to assign dates starting with 1/1/2025 and increasing by one month each time the running total for a given month hits 0.37. To make it more tricky, there are two different regions that need to each add up to half (0.185) of each month until one of them runs out, then contribute the full 0.37. If the numbers go over 0.37, that is preferable to cutting off a month prior to hitting 0.37.

Each region has a sub-region that has a priority level for assigning to earlier dates, but I believe that can just be figured out with sorting prior to assigning these bins.

Example data:

Region	Decimal	Month
A	0.1	1/1/2025
A	0.085	1/1/2025
A	0.2	2/1/2025
A	0.17	3/1/2025
B	0.18	1/1/2025
B	0.01	1/1/2025
B	0.185	2/1/2025
A	0.02	3/1/2025
B	0.19	3/1/2025

I'm relatively new to Alteryx so I'm not sure how to go about this. I tried to use the Tile tool to separate them into Equal Sum bins and get the bin number through taking the decimal column and dividing it by 0.37. Unfortunately that doesn't do much when it comes to getting the regions to split evenly. Help?

[TheMattLeonard · Accepted solution]

I'm having a bit of trouble understanding the exact logic in this part:

"there are two different regions that need to each add up to half (0.185) of each month until one of them runs out, then contribute the full 0.37. If the numbers go over 0.37, that is preferable to cutting off a month prior to hitting 0.37."

Either way though, I think you should check out the Running Total Tool to start. You can group on Region and then create a Running Total of the decimal field (you can also accomplish this with the Multi Row Formula Tool. It will be a bit more manual but you have more control over it).

From here I think you can do a formula tool with two new fields. The first is going to be how many months you want to add for each line. Expression should be "Floor([RunTot_Decimal]/.37)". The floor function is essentially just rounding down so it'll only move up a month once it actually gets over .37. Then you can actually calculate the Month field by using the expression "DateTimeAdd("2025-01-01",[Month Add],"Months")". Make sure the first field is formatted as double and the second is formatted as Date.

Hope this helps in at least getting you started.

[Spresza]

Definitely a good start. I split the regions to create separate running totals that roll over the total at 0.185 instead, but may need to refine it further on the part I wasn't clear on.

Each needs to make up 50% of that 0.37 running total. As those estimates are projected out, eventually one region will run out of locations to be estimated, and the remaining region will then use the full 0.37 for the running total. This isn't crucial because the regions are reasonably balanced, but it'd be nice to have.