Cumulative values with multi-row tool to create clusters

[danielrg]

Hello,

I am trying to create trucking routes based on constraints including driving hours and tank capacity. The routes have already been pre-determined by TSP. Now I simply need to slice and dice them based on accumulated quantity/driver hours. So, basically a running total. Once either constraint is reached I need the accumulation of delivery quantity to reset so a new driver can take over. I am translating a for-loop formula from VBA to simultaneously say if either constraint is exceeded then both the 'Cumulative Required Time to Serve Well' and the 'Cumulative Delivery Quantity in Cluster' reset to a value specified by other fields.The problem I am having is that I need to use [row-1] (I tried using the 'update existing field' but this does not work) which means that the final values that exceeds the limit (in this case 3000 gallons) delivery quantity remain as the first value of the next cluster. The next value resets. I need the current value to be replaced by the reset value.

I appreciate any insights that can be offered.
Thank you,
Daniel

[Claje · Accepted solution]

This was a fun one :)

I think you're right that you need a loop in order to correctly reset the data.

I created an Iterative Macro to accomplish this.  These are basically the Alteryx version of a For or While loop.

I simplified your multi-row formulas so that they simply created a running total for each of your criteria, and then used a filter to find cases where we exceeded either threshold.  I found the first case that exceeded this threshold, and output every record BEFORE that along with an ID number that could be used for downstream analysis, returning any remaining records into the loop for recalculation.

Let me know if you have any questions!

[danielrg]

Phenomenal. Thank you for solving this - and so quickly too! I am impressed - and inspired.