nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Rolling Sum in Formula Editor

scott1588

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.

Table.png

Formula.png

Accepted answers

txnelson

See if this will solve the issue for you

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

All comments

scott1588

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.

txnelson

See if this will solve the issue for you

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

scott1588

@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.

txnelson

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