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

- JMP User Community
- :
- Discussions
- :
- Rolling Sum in Formula Editor

- 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

Aug 22, 2020 7:27 PM
(461 views)

So I know that forms of this question have been asked before, but mostly the solutions has been JSL-based. One of my many failings in life is that I have not really learned JSL but instead rely on the formula editor (sorry). So I'm hoping there is a formula editor solution for this problem.

My goal is to get a formula that will generate a rolling sum. That is, I need a formula in one column that will generate a rolling sum of data in another column starting from the 8759th row before the current row all the way to the current row.

I have attached a copy of the formula I am trying to use but it does not seem to work. The attached table shows the problem. As soon as I get to a row containing zero, the cumulative sum is zero instead of 8759 (8759 ones plus one zero).

Can anyone help with this? Thanks in advance.

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

See if this will solve the issue for you

```
If( Row() == 1, sumx = 0 );
sumx = Sum( sumx, :Availability, -1 * :Availability[Row() - 8759] );
```

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: Rolling Sum in Formula Editor

I probably should have thought about this a bit more before posting... I can just do a rolling average and multiply by the lag, in this case 8760. I would be curious though if there are other solutions.

Highlighted

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

See if this will solve the issue for you

```
If( Row() == 1, sumx = 0 );
sumx = Sum( sumx, :Availability, -1 * :Availability[Row() - 8759] );
```

Jim

Highlighted
##

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

Re: Rolling Sum in Formula Editor

@txnelson This is great! Thanks. Works like a champ. But, of course... I have a question... I'm not sure why it works.

The IF statement assigns 0 to Row 1, got that. If the row is anything other than one, it leaves the cell blank, right?

The SUM statement then takes the current value of sumx, adds the current Availability value and subtracts the Availability value from row - 8759 and assigns the result back to sumx. I've got that.

But my question is... why does the current value of sumx show up in the cell? There is nothing in the formula (apparent to me) that says... "Show sumx". Does the assignment "sumx = ..." automatically imply in JMP that the result will display in the cell?

Sorry if this is a dumb question. But its understanding these little things that sometimes really helps me progress in my understanding of how JMP works.

Thanks again for you help on this.

Highlighted
##

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

Re: Rolling Sum in Formula Editor

The last value processed in a formula is what is returned from the formula and placed into the cell.

Jim