Calculations with running subtotals

Question

I am dealing with a challenge on how to translate Excel math to Alteryx.

My dataset has a City column, ID column (grouped), and a variance column. The target to calculate is the real 'variance to add' per station.

For all ID #1: the 'variance to add' is the variance as given.

For ID #2: the variance minus the 'variance to add' on row 1, but if this results in a negative number, the variance should be 0

For ID #3 the variance minus the sum of the 'variance to add' from ID 1 & 2, but if this results in a negative number, the 'variance to add' should be 0.

Essentially, it takes the variance of that record minus the running total of the 'variances to add'. If this results in a negative number, replace with 0.

Attached I have the Excel logic with formulas whereas the highlighted yellow column is the target output. Any idea on how to translate to Alteryx?

Variances to add logic.xlsx

Hsandness · Accepted Answer

@catmar ,

Record ID #1: For each city, the Variance to Add column is the variance given.

Record ID #2: The Variance to Add column is the Variance minus the Variance to Add of the previous column unless the previous Variance to Add is greater than record #2's Variance amount.

Record ID #3: The Variance to Add column is the record #3's variance amount minus the sum of the previous two record's Variance to Add values.

I used "Helper Column" to maintain the running subtotal amount of the Variance to Add column so that we could adjust the actual Variance to Add column. We just de-select the "Helper Column" at the end of the workflow and you have your desired output. Also, in Excel, you can achieve this by freezing the top record of the Variance to Add column by putting dollar signs before J and right before the number which will be the first record of each city. Please see screenshot for DFW.