Linking Data Filters to New Summary Tables

[#1]

I have a dataset with many continuous columns of data and two categorical factors that I filter on within my dashboard, and an additional Categorical variable (Group) that I use to split all the plots/analyses by. I also have a primary column switcher that means users can cycle through a summary table of each continuous variable. The primary column switcher is linked to all the other column switchers on the plots and analyses within the dashboard. So, any plot and analysis shows one continuous variable at a time and can be filtered by Sex (males/females or both).

This all works well, but the problem arises when I create a new table (dtSumm) that uses the summary statistic table for each continuous variable and calculates a new variable (Relative Change). This new summary table only has as many rows as there are categories of groups, and does not have Sex so we now lose the ability to filter by this.

The code for dtSumm is as follows:

// Create a separate datatable for treatment group means

dtSumm = dt << Summary (

       Group (:Group,:Sex ),

       Mean ( eval ( cols ) ),

       statistics column name format( "column" ),

       Link to original data table( 1 ),

       invisible

);



// Add RC columns for each column in cols to dtSumm

//This works by getting the mean of each column for treatment group "A"



For( i = 1, i <= num, i++,

       mean0 = Mean(Column(cols[i])[(dtSumm << get rows where( :"Group" == "A"))]);

       dtSumm << New Column(

             Char(cols[i]||"_RC"),

             Formula( ( ( ( column( cols[i] )[]) - ( eval(mean0) ) ) / ( eval(mean0) ) ) * 100 )         

       );

);

Any suggestions welcome. Thanks 

[#2]

It might help to add an example to demonstrate the question. (See JSL below)

The example uses the AdverseR data from the JMP sample data. The objective is to produce a dashboard like this:

You can see there is a Data Filter on SEX. And there is a column switcher that switches the "response" variable that is summarised in the two Tabulate reports and the Distribution.

The first Tabulate (left) gives the Mean response for each Treatment Group.

The second Tabulate (right) gives the mean % change in response for each Treatment Group relative to the PBO group. This runs from a summary table ("dtSumm") of the means of each response for each Treatment Group.

The problem is that the Data Filter works on everything apart from this second Tabulate report. What would be ideal is if dtSumm could summarise only the data that meets the Data Filer criteria. And dtSumm would be re-created when selections in the Data Filter change.

Is that possible?

// Open the data table
dt = Open( "$SAMPLE_DATA/AdverseR.jmp" );

//Define cols as the list of columns for column switcher to use
//This is essentially hard-coding the columns that will be used in the dashboard
//Edit the items in this list to change the columns used in the dashboard
cols = {"DAY ON DRUG", "AGE", "WEIGHT", "ADR DURATION"};

//Define num as the number of columns in the list, cols
num = length(cols);

// We create a separate data table for treatment group means
// Get table, dtSumm, of means by group for each column in cols

dtSumm = dt << Summary (
	Group (:Name("TREATMENT GROUP")),
	Mean ( eval( cols ) ),
	statistics column name format( "column" ),
	Link to original data table( 1 ),
	invisible
);

// Add RC columns for each column in cols to dtSumm
//This works by getting the mean for "PBO" treatment group for each 

For( i = 1, i <= num, i++,
	mean0 = Mean(Column(cols[i])[(dtSumm << get rows where( :"TREATMENT GROUP" == "PBO"))]);
	dtSumm << New Column( 
		Char(cols[i]||"_RC"), 
		Formula( ( ( ( column( cols[i] )[]) - ( eval(mean0) ) ) / ( eval(mean0) ) ) * 100 )		
	);
);





// Create dashboard with tables and Distribution plot
mainWindow=New Window( "Example",
			H List Box(
				dt << Current Data Table() << Data Filter(
					Location( {55, 48} ),
					Mode( Select( 0 ), Show( 1 ), Include( 1 ) ),
					Add Filter( columns( :SEX ) ) //
				);
				,
				table1= dt << Tabulate(
					Show Control Panel( 0 ),
					Add Table(
						Column Table(
							Analysis Columns(
								Column( cols[1] )
							),
							Statistics( Mean, N )
						),
						Row Table( Grouping Columns( :TREATMENT GROUP ) )
					)
				);
				,
				table2= dtSumm << Tabulate(
					Change Item Label( Statistics( Mean, "%Rel Change" ) ),//Changes heading from "Mean"
					Show Control Panel( 0 ),
					Add Table(
						Column Table( Analysis Columns( Char(cols[1]||"_RC") ), Statistics( Mean ) ),
						Row Table( Grouping Columns( :TREATMENT GROUP ) )
					)
				);
				,
				dist0 = dt << Distribution(
					Continuous Distribution(
						Column( cols[1] ), //dt_c_col[1] uses first in column list
						Always use column properties( 1 ),
						Process Capability( 0 )
					),
					Histograms Only,
					SendToReport(
						Dispatch( {}, "Distribution", OutlineBox ),
						Dispatch( {}, "Process 1", OutlineBox, {Close( 1 )} )
					)
				)
			)
		);

// Add a column switcher to table1
cs = table1 << Column Switcher(
	cols[1],
	cols
);

// Link column switchers at each platform - JMP 15 solution

cs1 = table2 << Column Switcher(
	Char(cols[1]||"_RC"),
	eval list(dtSumm << Get Column Names( String ))[(num+3)::(num+2+num)],
	Close Outline( 1 )
);

cs2 = dist0 << Column Switcher(
	cols[1],
	cols,
	Close Outline( 1 )
);

mainWindow[ListBoxBox( 1 )] << Set Script(
	var = mainWindow[ListBoxBox( 1 )] << Get Selected;
	cs1 << Set Current(Char(var[1]||"_RC"));
	cs2 << Set Current(var[1]);
);

[#3]

Probably there's a better way now, but in the past I've used 'makeFilterChangeHandler()'. So, in outline:

NamesDefaultToHere(1);

dt = Open("$SAMPLE_DATA/Big Class.jmp");

// Report involving detail-level data
NewWindow("Detail and Summary Data with Local Data Filter", << onClose(Try(Close(dt2, noSave))),
	hlb = HListBox(dist = dt << Distribution(Continuous Distribution( Column( :height ) )));
	);
// Local data filter
ldf = dist << Local Data Filter( Add Filter( columns( :sex ) ) );

// This script will run each time the filter selection is changed
ldfChange =
	Function( {a},
		filteredRows = ldf << getFilteredRows;
		// Try to close any open ancilary tables
		Try(Close(dt2, noSave));
		// Make a subset table according to the current selection
		dt2 = dt << subset(Rows(filteredRows), Invisible);
		// Aggregate the subset table
		dt3 = dt2 << summary(Mean(:height), Invisible);
		// Update the report
		hlb << append(dt3 << Tabulate(Show Control Panel( 0 ), Add Table(Column Table( Analysis Columns( :"Mean(height)"n ), Statistics( Mean ) ))));
);

// Attach the script to the local data filter
rsh = ldf << makeFilterChangeHandler(ldfChange);