Calculate the amount to a specific value and any remaining to calculated

[DanielCarro]

Hi guys,

I am in a situation which I need some help on Alteryx.

I have the following situation:

Employee	Share Purchase Date	Amount of Shares
111	03/05/2013	5
111	17/11/2012	14
111	10/11/2012	10
111	07/07/2012	5
111	04/04/2012	6
111	15/11/2011	15
111	05/10/2011	10
111	02/03/2011	10
111	01/02/2010	5

So I need to pick the first 45 shares (in this case the earliest shares purchased), sum up until 45 shares, then recalculate to the number of shares are correct and in line. So my goal is to achieve the 'new amount of shares' column

Employee	Share Purchase Date	Amount of Shares	First 45 shares	New Amount of Shares
111	03/05/2013	5		5
111	17/11/2012	14		14
111	10/11/2012	10		10
111	07/07/2012	5		5
111	04/04/2012	6	5	1
111	15/11/2011	15	15	0
111	05/10/2011	10	10	0
111	02/03/2011	10	10	0
111	01/02/2010	5	5	0

Much appreciated for any help.

Kind Regards,

Dan

[phottovy · Accepted solution]

Hi @DanielCarro ,

Here is one way of accomplishing the calculation. I added a second input in case not all employees have the same 45 shares. 

[DanielCarro]

Hi @phottovy 

Amazing, worked like a charm

[DanielCarro]

Thank you @phottovy