Accumulation

[#1]

Hi,

I am attaching my excel,

here ws1 is the field which i need to devide based on quantity.

i am getting like this         

Part No	Posting Date	Quantity	Total Qty	ws1	Cumulative		outfield
000979940	4/1/2016	4	234	158	4		4
000979940	3/31/2016	100	234	158	104		100
000979940	3/16/2016	16	234	158	120		16
000979940	3/2/2016	100	234	158	220		38
000979940	2/18/2016	4	234	158	224		38
000979940	2/16/2016	17	234	158	241		38
000979940	5/23/2015	-4	234	158	237		38
000979940	5/22/2015	-20	234	158	217		38
000979940	5/21/2015	-2	234	158	215		38
000979940	5/20/2015	-7	234	158	208		38
000979940	5/19/2015	-4	234	158	204		38
000979940	5/18/2015	-6	234	158	198		38
000979940	5/16/2015	-16	234	158	182		38
000979940	5/15/2015	-4	234	158	178		38
000979940	5/14/2015	-8	234	158	170		38
000979940	5/12/2015	-12	234	158	158		-12
000979940	11/20/2014	2	234	158	160		38
000979940	10/23/2014	70	234	158	230		38
000979940	10/21/2014	13	234	158	243		38
000979940	5/21/2014	-8	234	158	235		38
000979940	11/12/2013	-1	234	158	234		0

i want output like below.

my alocated it has to show 158 because ws1 is 158.

outfield should get from quantity.

Part No	Posting Date	Quantity	Total Qty	ws1	Cumulative		outfield
000979940	4/1/2016	4	234	158	4		4
000979940	3/31/2016	100	234	158	104		100
000979940	3/16/2016	16	234	158	120		16
000979940	3/2/2016	100	234	158	220		38
000979940	2/18/2016	4	234	158	224		0
000979940	2/16/2016	17	234	158	241		0
000979940	5/23/2015	-4	234	158	237		0
000979940	5/22/2015	-20	234	158	217		0
000979940	5/21/2015	-2	234	158	215		0
000979940	5/20/2015	-7	234	158	208		0
000979940	5/19/2015	-4	234	158	204		0
000979940	5/18/2015	-6	234	158	198		0
000979940	5/16/2015	-16	234	158	182		0
000979940	5/15/2015	-4	234	158	178		0
000979940	5/14/2015	-8	234	158	170		0
000979940	5/12/2015	-12	234	158	158		0
000979940	11/20/2014	2	234	158	160		0
000979940	10/23/2014	70	234	158	230		0
000979940	10/21/2014	13	234	158	243		0
000979940	5/21/2014	-8	234	158	235		0
000979940	11/12/2013	-1	234	158	234		0

[Kushal_Chawda]

see this

Data:
LOAD [Part No],
[Posting Date],
if(Cumulative<= ws1,1,0) as Flag,
Quantity,
[Total Qty],
ws1,
Cumulative
FROM
[Accumulation.xlsx]
(ooxml, embedded labels, table is Sheet1);

Accu:
LOAD *,
if(Flag=0 and Previous(Flag)=1,Outfield2,
if(Flag=0 and Previous(Flag)=0,ws1-(Outfield1+Outfield2),
if(Cumulative= ws1,0,Outfield1))) as Outfield;
LOAD *,
if(Flag=0 and Previous(Flag)=1, ws1-Outfield1, if(Flag=0 and Previous(Flag)=0, Peek('Outfield2'),0)) as Outfield2;
LOAD *,
if(Flag=1,Quantity,
if(Flag=0 and Previous(Flag)=1, Peek('Cumulative'),
if(Flag=0 and Previous(Flag)=0, Peek('Outfield1')))) as Outfield1
Resident Data
Order by ws1,[Posting Date] desc ;

DROP Table Data;