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

[#1]

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.

[#2 · Accepted solution]

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

[#3]

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

[#4]

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

[#5]

Hello, I face the same issue today and tried the solution of @txnelson. Unfortunately the formula is not updated when I use it with a local data filter. Did I miss something? Or is the solution not working anymore with JMP 16?

[#6]

The formula in the data table doesn't know the selection of the Local Data Filter of your report.

More surprisingly, this is also true for a transform column in a report - it just ignores the selection of the local data filter of the respective report.

We have become so accustomed to what the summary statistics in graph builder do - automatically.
With this background, the behavior of Column Formulas feels like a bug:
Transform Column: Bug with excluded rows 

With the words of Jmp Support (TS-00056243)

It appears the transform column formula is not re-evaluated in response to Local Data Filter changes like it is for Global Data Filter changes. I will report this as a bug.  Thank you for pointing it out.

After a while I got the feedback:
The issue has been reported and as you assume, development shares that this is behaving as designed, where the transform columns behave just like regular table columns with respect to honoring row states.

Concerning future versions of Jmp, there is an entry in the wish list which suggests to add an option to not ignore the local data filter:

Transform Columns - as comfortable as Summary Statistics? 

As a workaround with current Jmp, you have to use a REAL column (not a transform column (*)), ask the Local Data Filter for the selected rows via JSL and use the response as an input for the formula:
https://community.jmp.com/t5/Discussions/Transform-Column-Bug-with-excluded-rows/m-p/676095/highligh... 

[#7]

(*) Along the idea: don't pollute the data table 
... I wondered if it is possible to use a Transform Column instead of a real column.

Unfortunately, this is not possible:

https://community.jmp.com/t5/Discussions/how-to-reference-a-Transform-Column/m-p/691608/highlight/tr... 

So, at the moment one has to either use a "real column" (see the previous post) - or accept that the solution with a Transform Column is as "automatic" as re-creating the column every time the selection in the local Data filter is changed.