Create Calculated Field that Subtracts Field 1 from Field 2 (within same Column)

Question

I would like to create a calculated field (as displayed in column E) that locates the lowest system ID (column B)  within a project ID (column A) and populates "E3" with the number found in the combined hours (column C) column. "E4" should be "C4" minus "E3". "E5" should be "C5" minus sum(E3:E4).

The process should be repeated for Project ID 2 starting in "E6".

I would like to point out, that system IDs are generated on a +1 basis, so you would never see a system ID that is smaller than its predecessor (within the same project ID)

I look forward to your responds- thank you in advance for the support.

Sample_Data_Alteryx_Community.xlsx

cmcclellan · Accepted Answer

I'm pretty sure this is what you're after :)

MarshallG · Accepted Answer

They key to this is using the multi-row formula. First, sort by your project_ID and System_ID (you've already done that for us with your sample data) and then use a multi-row formula to compare the previous row's value for Combined_Hours. Workflow attached.