topic Re: Script to do cumulative sum until meet certain limit in Discussions

Script to do cumulative sum until meet certain limit

Lino — Fri, 09 Jun 2023 23:37:55 GMT

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

Re: Script to do cumulative sum until meet certain limit

txnelson — Mon, 14 Sep 2020 17:47:35 GMT

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

Re: Script to do cumulative sum until meet certain limit

Lino — Tue, 15 Sep 2020 13:39:29 GMT

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.

Re: Script to do cumulative sum until meet certain limit

txnelson — Tue, 15 Sep 2020 14:26:34 GMT

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

Re: Script to do cumulative sum until meet certain limit

Lino — Wed, 16 Sep 2020 14:55:31 GMT

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.