Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
MrSmith
Level I

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

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.

screen1.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

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

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

View solution in original post

3 REPLIES 3
Highlighted
txnelson
Super User

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

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

View solution in original post

Highlighted
MrSmith
Level I

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
txnelson
Super User

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.