306813_en-US

liliconcom

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

 

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

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.

 

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