cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Lino
Level III

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
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
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
Lino
Level III

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

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
Lino
Level III

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.