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

MrSmith

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

txnelson

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