Choose Language Hide Translation Bar
Highlighted
Lino
Level II

Script to do cumulative sum until meet certain limit

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: Script to do cumulative sum until meet certain limit

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];
		
	)
);
Jim

View solution in original post

4 REPLIES 4
Highlighted
txnelson
Super User

Re: Script to do cumulative sum until meet certain limit

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];
		
	)
);
Jim
Highlighted
Lino
Level II

Re: Script to do cumulative sum until meet certain limit

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.

 Capture1.JPG

Highlighted
txnelson
Super User

Re: Script to do cumulative sum until meet certain limit

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];
		
	)
);
Jim

View solution in original post

Highlighted
Lino
Level II

Re: Script to do cumulative sum until meet certain limit

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.

Article Labels