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; } } }

Cumulative Summation Formula Within a Column?

Peter_Bartell

Ok...What am I doing wrong here...I'd like to create a new column for which each row is the cumulative sum of the rows in another column. I've attached a data table with my original column 1, formula column 2 and a column 3 what I want the desired result to look like. Below is what jmp Help says my formula should look like...what am I missing? It's got to be obvious and I'm just not seeing it?

ColumnSummationFormula.jmp

Accepted answers

Chris_Kirchberg

This is what the column formula should look like. The trick is to change N Row() to Row() and add a subscript to the column name and name it “I”

Chris Kirchberg

JMP Technical Enablement Engineer

Worldwide Technical Enablement Team

SAS Institute, Inc.

100 SAS Campus Drive | Building T | Cary, NC 27513

Tel: +1-919-531-9927<tel:%2B1-919-531-9927>

Mobile: +1-303-378-7419<tel:%2B1-303-378-7419>

E-mail: chris.kirchberg@jmp.com<mailto:chris.kirchberg@jmp.com>

Unsubscribe<http://www.sas.com/apps/jmp/email_preference.jsp>

image001.png

All comments

Chris_Kirchberg

This is what the column formula should look like. The trick is to change N Row() to Row() and add a subscript to the column name and name it “I”

Chris Kirchberg

JMP Technical Enablement Engineer

Worldwide Technical Enablement Team

SAS Institute, Inc.

100 SAS Campus Drive | Building T | Cary, NC 27513

Tel: +1-919-531-9927<tel:%2B1-919-531-9927>

Mobile: +1-303-378-7419<tel:%2B1-303-378-7419>

E-mail: chris.kirchberg@jmp.com<mailto:chris.kirchberg@jmp.com>

Unsubscribe<http://www.sas.com/apps/jmp/email_preference.jsp>

image001.png

brady_brady

You need an "i", not a "1" as the subscript for your Revenue column.

Hence something like this:

Summation(i = 1, Row(), :Column 1)

Instead of this:

Summation(i = 1, Row(), :Column 1[1])

Note, you can now do CumSum in JMP 12 with a 1-click column formula: Right-click on the column, New Formula Column > Row > Cumulative Sum.

This procedure uses the following, alternative formula:

If(Row() == 1, 0, Lag()) + If(Is Missing(:Column 1), 0, :Column 1)

Peter_Bartell

Arggghhhhh...I'm embarrassed to have even asked the question. Thanks Chris!

Chris_Kirchberg

If JMP 12, do what Brady mentioned. Much easier.

Chris Kirchberg

JMP Technical Enablement Engineer

Worldwide Technical Enablement Team

SAS Institute, Inc.

100 SAS Campus Drive | Building T | Cary, NC 27513

Tel: +1-919-531-9927<tel:%2B1-919-531-9927>

Mobile: +1-303-378-7419<tel:%2B1-303-378-7419>

E-mail: chris.kirchberg@jmp.com<mailto:chris.kirchberg@jmp.com>

Unsubscribe<http://www.sas.com/apps/jmp/email_preference.jsp>

Jeff_Perkinson

I'd also point out that for large tables using the Summation function for this is very inefficient, as it recalculates the summation of all of the rows in the table up to the current row for every row.

Instead the formula used by New Formula Column > Row > Cumulative Sum is more efficient.

See Re: Efficient moving average algorithm for more discussion.

-Jeff