Script to do cumulative sum until meet certain limit

[#1]

My data table starting with 3 columns, Date, Test_Qty and Fail_Qty.
I am creating script for 2 new columns, Cumulative_Sum_Test_Qty and Cumulative_Fail_Qty.
In Cumulative_Sum_Test_Qty column, the test qty is cumulative sum from current rows to previous row to meet 300 qty to form one lot of data.
In Cumulative_Sum_Fail_Qty, the fail qty is cumulative sum from current row to previous rows corresponding to cumulative sum test qty take place.

I am get stuck when creating the script for the cumulative sum for the both columns. 

Can anyone provide help here

[#2]

Here is a solution.  I am assuming as soon as you go over 300 you restart.  If you need to stop before you go over 300, the logic change is pretty simple.

Names Default To Here( 1 );
dt = Current Data Table();
dt << New Column( "Cumulative_Sum_Test_Qty" );
dt << New Column( "Cumulative_Sum_Fail_Qty" );

dt:Cumulative_Sum_Test_Qty[1] = dt:Test_Qty[1];
dt:Cumulative_Sum_Fail_Qty[1] = dt:Fail_Qty[1];

holdTest = dt:Test_Qty[1];
holdFail = dt:Fail_Qty[1];

For( theRow = 2, theRow <= N Rows( dt ), theRow++,
	If( dt:Cumulative_Sum_Test_Qty[theRow - 1] > 300,
		dt:Cumulative_Sum_Test_Qty[theRow] = dt:Test_Qty[theRow];
		dt:Cumulative_Sum_Fail_Qty[theRow] = dt:Fail_Qty[theRow];
	,
		dt:Cumulative_Sum_Test_Qty[theRow] = dt:Cumulative_Sum_Test_Qty[theRow - 1] + dt:Test_Qty[theRow];
		dt:Cumulative_Sum_Fail_Qty[theRow] = dt:Cumulative_Sum_Fail_Qty[theRow - 1] + dt:Fail_Qty[theRow];
		
	)
);

[#3]

Thanks Jim.

What I needed is after go over 300, in the following row, new cumulative sum take place again until go over 300,...

It is kind of backward moving cumulative sum.....  This where I get stuck.

 

[#4 · Accepted solution]

OK.....I appreciate the clearer definition.  The script below seems to work on your test data

Names Default To Here( 1 );
dt = Current Data Table();
dt << New Column( "Cumulative_Sum_Test_Qty" );
dt << New Column( "Cumulative_Sum_Fail_Qty" );

dt:Cumulative_Sum_Test_Qty[1] = dt:Test_Qty[1];
dt:Cumulative_Sum_Fail_Qty[1] = dt:Fail_Qty[1];

holdTest = dt:Test_Qty[1];
holdFail = dt:Fail_Qty[1];

For( theRow = 2, theRow <= N Rows( dt ), theRow++,
	If( dt:Cumulative_Sum_Test_Qty[theRow - 1] > 300,
		holdTest = :Test_Qty[theRow];
		holdFail = :Fail_Qty[theRow];
		curRow = theRow;
		While( holdTest < 300,
			curRow = curRow - 1;
			holdTest = holdTest + :Test_Qty[curRow];
			holdFail = holdFail + :Fail_Qty[curRow];
		);
		dt:Cumulative_Sum_Test_Qty[theRow] = holdTest;
		dt:Cumulative_Sum_Fail_Qty[theRow] = holdFail;
	,
		dt:Cumulative_Sum_Test_Qty[theRow] = dt:Cumulative_Sum_Test_Qty[theRow - 1] + dt:Test_Qty[theRow];
		dt:Cumulative_Sum_Fail_Qty[theRow] = dt:Cumulative_Sum_Fail_Qty[theRow - 1] + dt:Fail_Qty[theRow];
		
	)
);

[#5]

Thanks Jim. It works.

I study the script logic you provided. This help me enhance my knowledge.

I appreciate your help and guidance. 

Thank you.