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

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

Highlighted

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

Aug 8, 2019 11:51 AM
(1089 views)

Hi everyone,

I'm trying to create a column which evaluates it's previous row and adds the zscore to it and then takes the maximum between this evaluation and 0. I've tried moving the lag formula around the Cumulative Sum equation but have not had any luck. It is only taking the value of zscore regardless of the previous row. Any help on how to resolve this would be greatly appreciated!

`dt = Open("Big Class.jmp");`

dt << New Column( "zscore", Formula((:weight - Col Mean( :weight, :sex )) / Col Std Dev( :weight, :sex )));

dt << Sort( By( :sex), Order( Ascending), Replace Table );

dt << New Column( "S_Hi", Formula( If( :sex == Lag( :sex ), Max( 0, :zscore + Lag( Col Cumulative Sum( :S_Hi ) ) ), Max( 0, :zscore ) ) ) )

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

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

I think that Jim's solution is based on your original script but I don't think you want to use a separate cumulative sum. The following script is not correct because I am still not quite sure what you want but it is simpler and perhaps closer to the real solution:

```
Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/big class.jmp" );
dt << Sort( By( :sex ), Order( Ascending ), Replace Table );
dt << New Column( "Z Score", Formula( (:weight - Col Mean( :weight, :sex )) / Col Std Dev( :weight, :sex ) ) );
dt << New Column( "S_Hi",
Formula( If( :sex == Lag( :sex ), Max( 0, :zscore + Lag( :S_Hi ) ), Max( 0, :zscore ) ) )
);
```

Learn it once, use it forever!

4 REPLIES 4

Highlighted
##

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

Re: Gathering the cumulative sum from evaluated column

Here is how I would approach this

```
Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/big class.jmp" );
dt << New Column( "zscore",
Formula( (:weight - Col Mean( :weight, :sex )) / Col Std Dev( :weight, :sex ) )
);
dt << Sort( By( :sex ), Order( Ascending ), Replace Table );
dt << New Column( "S_Hi",
Formula(
If(row()==1,cum=0);
If( :sex == Lag( :sex ),
x=Max( 0, :zscore + cum ),
x=Max( 0, :zscore )
);
cum=cum+x;
x;
)
);
```

Jim

Highlighted
##

Thanks for the help Nelson, but unfortunately the Max value never kicks in. When applied, the 40th row is alread a 5E+10 value. In essense I just want to get the sum of all previous values in the column and add them to the zscore at that row.

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

Re: Gathering the cumulative sum from evaluated column

Highlighted

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

I think that Jim's solution is based on your original script but I don't think you want to use a separate cumulative sum. The following script is not correct because I am still not quite sure what you want but it is simpler and perhaps closer to the real solution:

```
Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/big class.jmp" );
dt << Sort( By( :sex ), Order( Ascending ), Replace Table );
dt << New Column( "Z Score", Formula( (:weight - Col Mean( :weight, :sex )) / Col Std Dev( :weight, :sex ) ) );
dt << New Column( "S_Hi",
Formula( If( :sex == Lag( :sex ), Max( 0, :zscore + Lag( :S_Hi ) ), Max( 0, :zscore ) ) )
);
```

Learn it once, use it forever!

Highlighted
##

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

Re: Gathering the cumulative sum from evaluated column

Hey Mark, this worked out perfectly. Thanks for the feedback!

Article Labels

There are no labels assigned to this post.