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!!!
Résolu ! Accéder à la 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!!