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
- :
- help creating a column that is a cumulative sum ba...

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

Aug 17, 2016 11:51 AM
(1775 views)

2 ACCEPTED SOLUTIONS

Accepted Solutions

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

Aug 17, 2016 1:31 PM
(2114 views)

Solution

hi aporter,

until someone smarter comes up with a one liner you can try my plumbers solution:

Names Default To Here**(** **1** **)**;

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

dt << **New Column(** "Row", formula**(** :Row**()** **)**, eval formula **)**;

dt:row << **delete formula**;

// assuming age is time and sex is site

dt << **Sort(** By**(** :sex, :age **)**, Order**(** Ascending, Ascending **)**, replace table **)**;

// now sum as you want it

dt << **New Column(** "sumhightbysex",

formula**(** If**(** Row**()** == **1**, :height, :sex == Lag**(** :sex, **1** **)**, :height + Lag**(** :height, **1** **)**, :sex != Lag**(** :sex, **1** **)**, :height, Empty**()** **)** **)**, eval formula

**)**;

dt:sumhightbysex << **delete formula**;

// sort to original order

dt << **Sort(** By**(** :Row **)**, Order**(** Ascending **)**, replace table **)**;

// clean up

dt << **delete column** **(**row**)**;

Speak**(** "bob's your uncle" **)**;

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

Sep 8, 2016 1:11 PM
(2113 views)

Solution

Hi Ron,

This is a great solution, thanks for posting.

The formula for the new column sumheightbysex has a slight typo. It should be

If(Row() == 1, :height, :sex == Lag(:sex, 1), :height + Lag(:sumhightbysex, 1), :sex != Lag(:sex, 1), :height, Empty())

using the value in the new column to add to the height, and not the height from previous row, :height + Lag(:height), 1).

G'day

4 REPLIES

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

Aug 17, 2016 1:18 PM
(1603 views)

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

Aug 17, 2016 1:31 PM
(2115 views)

hi aporter,

until someone smarter comes up with a one liner you can try my plumbers solution:

Names Default To Here**(** **1** **)**;

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

dt << **New Column(** "Row", formula**(** :Row**()** **)**, eval formula **)**;

dt:row << **delete formula**;

// assuming age is time and sex is site

dt << **Sort(** By**(** :sex, :age **)**, Order**(** Ascending, Ascending **)**, replace table **)**;

// now sum as you want it

dt << **New Column(** "sumhightbysex",

formula**(** If**(** Row**()** == **1**, :height, :sex == Lag**(** :sex, **1** **)**, :height + Lag**(** :height, **1** **)**, :sex != Lag**(** :sex, **1** **)**, :height, Empty**()** **)** **)**, eval formula

**)**;

dt:sumhightbysex << **delete formula**;

// sort to original order

dt << **Sort(** By**(** :Row **)**, Order**(** Ascending **)**, replace table **)**;

// clean up

dt << **delete column** **(**row**)**;

Speak**(** "bob's your uncle" **)**;

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

Sep 8, 2016 1:11 PM
(2114 views)

Hi Ron,

This is a great solution, thanks for posting.

The formula for the new column sumheightbysex has a slight typo. It should be

If(Row() == 1, :height, :sex == Lag(:sex, 1), :height + Lag(:sumhightbysex, 1), :sex != Lag(:sex, 1), :height, Empty())

using the value in the new column to add to the height, and not the height from previous row, :height + Lag(:height), 1).

G'day

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

Sep 12, 2016 9:10 AM
(1603 views)

glad you find it useful,

Perhaps the oneliner can be based on this:

Re: Syntax: How do I create an index column by ID

it would also make it independent of sorting and maintain an active formula.

but i am not sure how to tackle it.

Best