Community Trekker

Joined:

Sep 29, 2014

## Cum Sum By Variable1 Variable2 Variable3

I searched but I couldn't find a formula fro Cumulative Summary by Variables (variable1, variable2, variable3....) like let's say "ColSum" function.

I asked few times here at discussion board and thanks a lot the developers were very helpful, however the variables being char or num changes the final result. As a JMP user  I would be very happy if a CumSum by Variables function would be available under statistics group formula (just a suggestion).

May I ask which formulas should I write in column formula for:

CumSum for 3 variables (variable1, variable2, variable3) if all the variables are character?

CumSum for 3 variables (variable1, variable2, variable3) if all the variables are numeric?

CumSum for 3 variables (variable1, variable2, variable3) if some variables are character and some variables are numeric?

4 REPLIES

Super User

Joined:

Jun 23, 2011

## Re: Cum Sum By Variable1 Variable2 Variable3

What was the method you used that depends on data type?

If you are looking for a column formula solution, try the Col Sum() function. It is very versatile.

Se below example of how to use ColSum() to give a cumulative sum with two by-variables. Doesn't matter whether the by-variables are character or numeric. The sum-variable must of course be continuous.

dt = Open("\$SAMPLE_DATA/Big Class.jmp");

F = Expr(Eval(Eval Expr(Col Sum(:weight, Row() <= Expr(Row()), :sex, :age))));

dt << New Column("CumSum of weight by sex and age", numeric, formula(Name Expr(F)));

Community Trekker

Joined:

Sep 29, 2014

## Re: Cum Sum By Variable1 Variable2 Variable3

Dear MS,

The solution is genius, thank you! It even accepts the missing values as null.

However it is an incredible "processor power hungry" formula. With a mid 2010 Mac creating a new formula column of 3600 wows by one variable took 20 mins. With a 124000 rows I went to sleep after 1 hour, and it was ready when I woke up...

Staff

Joined:

Jun 23, 2011

## Re: Cum Sum By Variable1 Variable2 Variable3

Your description is somewhat general, so I will offer general suggestions. Have your tried Tables > Summary? Have you tried Analyze > Tabulate? Have you tried Summarize() in a script?

Learn it once, use it forever!

Community Trekker

Joined:

Sep 29, 2014

## Re: Cum Sum By Variable1 Variable2 Variable3

Dear MarkBailey,

None of them help me, the formula is need for adding new column to a table which is also a summary. Moreover I do not know if there is any kind of Summary/Tabulate which can replace a data with Cumulative Summary