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

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

MrSmith — Mon, 26 Mar 2018 14:04:04 GMT

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.

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

txnelson — Mon, 26 Mar 2018 13:03:21 GMT

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

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

MrSmith — Mon, 26 Mar 2018 14:43:02 GMT

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

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

txnelson — Mon, 26 Mar 2018 14:50:43 GMT

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

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

anne_sa — Mon, 29 Nov 2021 10:03:52 GMT

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?

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

hogi — Sat, 28 Oct 2023 15:48:34 GMT

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:

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:

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/highlight/true#M86284

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

hogi — Sat, 28 Oct 2023 14:36:16 GMT

(*) 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/true#M87735

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.