Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- JMP User Community
- :
- Discussions
- :
- Re: Script to do cumulative sum until meet certain limit

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Sep 14, 2020 7:07 AM
(167 views)

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

4 REPLIES 4

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.