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!!!
Solved! Go to 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]
Thank you so much, this works perfect!!