pick next available data fill in new created column and deduct above picked number

[lzhang775]

Hi,

Anyone has an idea on last column in below table? I need to pick available QTY for each GroupID, but need to deduct picked numbers from above rows in the new created column. 

Thank you in advance for your help!!!

[cgoodman3 · Accepted solution]

Hi

Alteryx works best with complete information on each row (record).

First: use a multirow formula to bring the qty down in the the “Quantity for each group” column:

group by Group ID

Expression:  IF [RowCount] = 1 THEN [Quantity for each group] ELSE [Row-1:Quantity for each group] ENDIF

Second use another multi row to calculate the running total allocated, create a new field called Allocated

Group by: RowCount

Expression: IF [RowCount] = 1 AND [Avaliable QTY by year] <= [Quantity for each group] THEN [Avaliable QTY by year]
ELSEIF [RowCount] = 1 AND [Avaliable QTY by year] > [Quantity for each group] THEN [Quantity for each group]
ELSEIF ([Avaliable QTY by year]+[Row-1:Allocated]) <= [Quantity for each group] THEN [Avaliable QTY by year] + [Row-1:Allocated]
ELSEIF ([Avaliable QTY by year]+[Row-1:Allocated]) > [Quantity for each group] THEN [Quantity for each group]
ELSE 0
ENDIF

third you need another multi row formula to show what was actually allocated, create a new field called QTY pick

Group by: GroupID

Expression: [Allocated]-[Row-1:Allocated]

[lzhang775]

Thank you so much, this works perfect!!