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

- JMP User Community
- :
- Discussions
- :
- Re-evaluate a Formula for a report based on the subset on the data selected in L...

- 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

Created:
Mar 26, 2018 12:50 AM
| Last Modified: Mar 26, 2018 7:04 AM
(4855 views)

Dears,

Can anybody give a hint, if it is possible to re-evaluate a Formula for a report based on the subset on the data selected in Local Data Filter?

E.g. my formula is

`Col Sum(:aval, :ID)`

In the screenshot attached, only line9 meets the local data filter conditions. Is it possile to create a formula which would return 1 instead of 2 (since within ID= V2_7 only 1 record with aval = 1 would be summed)?

I would strongly prefer an intercative solution (without actually subsetting the table or creating summary table). I learned that the Col ... formulas can caluculate the statistics based on the "Selected()" Row state, therefore I suppose there could be a way of doing something similar based on the Local Data Filer subset.

Another solution which could partially meet my requirements would be availablility of N Categories(:var) Summary Statistic in the Graph Builder, but as far as I understand it's not available yet.

Thanks a lot!

Alex.

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

By using an If() clause in the Col Sum() function, you can exclude the Excluded rows

```
Names Default To Here( 1 );
// Open the data table
dt = Open( "$SAMPLE_DATA\Blood Pressure.jmp" );
// Set it up to look like your data
dt << delete columns(
{"BP 12M", "BP 6M", "BP 8W", "BP 12W", "BP 6W", "BP 8F", "BP 12F",
"BP 6F"}
);
dt:Subject << set name( "ID" );
dt:BP 8M << set name( "aval" );
// Create the new column
dt << New Column( "myCol",
formula( Col Sum( If( Excluded( Row State( Row() ) ), ., :aval ), :ID ) )
);
// Apply a filter
dt << Data Filter(
Location( {809, 218} ),
Mode( Show( 1 ), Include( 1 ) ),
Add Filter(
columns( :Dose ),
Display( :Dose, Size( 160, 60 ), List Display )
)
);
```

Jim

3 REPLIES 3

Highlighted

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

By using an If() clause in the Col Sum() function, you can exclude the Excluded rows

```
Names Default To Here( 1 );
// Open the data table
dt = Open( "$SAMPLE_DATA\Blood Pressure.jmp" );
// Set it up to look like your data
dt << delete columns(
{"BP 12M", "BP 6M", "BP 8W", "BP 12W", "BP 6W", "BP 8F", "BP 12F",
"BP 6F"}
);
dt:Subject << set name( "ID" );
dt:BP 8M << set name( "aval" );
// Create the new column
dt << New Column( "myCol",
formula( Col Sum( If( Excluded( Row State( Row() ) ), ., :aval ), :ID ) )
);
// Apply a filter
dt << Data Filter(
Location( {809, 218} ),
Mode( Show( 1 ), Include( 1 ) ),
Add Filter(
columns( :Dose ),
Display( :Dose, Size( 160, 60 ), List Display )
)
);
```

Jim

Highlighted
##

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

Re: Re-evaluate a Formula for a report based on the subset on the data selected in Local Data Filter

Thanks a lot Jim!

In fact, what I was looking for is the same functionality directly in the Local Data Filter of the Graph Builder, however this solution should work as well as soon as I can fit this Data Filter together with the Data Table and Graph into a DashBoard (just need to play around a little with this).

Highlighted
##

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

Re: Re-evaluate a Formula for a report based on the subset on the data selected in Local Data Filter

use your local data filter....the formula will work with it too. I used the global filter because I didn't have a platform open

Jim

Article Labels

There are no labels assigned to this post.