turn on suggestions

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

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Cum Sum By Variable1 Variable2 Variable3

Topic Options

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

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

Apr 16, 2015 2:31 AM
(1670 views)

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?

Many thanks in advance.

4 REPLIES

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

Apr 16, 2015 3:57 AM
(1418 views)

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**)))**;

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

Apr 22, 2015 5:26 AM
(1418 views)

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

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

Apr 19, 2015 7:57 AM
(1418 views)

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!

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

Apr 22, 2015 5:23 AM
(1418 views)

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