Exclude rows in Col Quantile Calculation

[#1]

Hi,

I was trying to get Col Quantile( :height, 0.5, :class) but it looks like the calculation would still take the excluded rows into consideration. 

Is there a way to reject the excluded rows into Col Quantile calculation?

Thanks,

Midori

[#2 · Accepted solution]

You can check for the row being excluded within the Col Quantile() function.  Below is an example

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

dt << New Column( "Col Quantile Height by Age",
	numeric,
	continuous,
	formula( Col Quantile(If(excluded(rowstate(Row())),., :height), 0.5, :age ) )
);

Whenever the excluded rowstate changes for the rows in the data table, the formula will calculate new values

[#3]

I tried to create a loop to collect the 50th percentile of many parameters using the following code, but both elements in "test_list" are the same (they are both 50% of weight). However, if I run the lines manually, it will work. Can I not use the Col Quantile() function in the for loop?

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

plot_param={
	
	height,
	weight};

test_list={};

for(plotcnt=1, plotcnt<=nitems(plot_param), plotcnt++,

	pct_50=Col Quantile( If(excluded(rowstate(Row())),., column(plot_param[plotcnt])), 0.5);
	test_list[plotcnt]=pct_50;
);

[#4]

I tried to create a loop to collect the 50th percentile of many parameters using the following code, but both elements in "test_list" are the same (they are both 50% of weight). However, if I run the lines manually, it will work. Can I not use the Col Quantile() function in the for loop?

Names Default To Here( 1 );

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

plot_param={

height,

weight};

test_list={};

for(plotcnt=1, plotcnt<=nitems(plot_param), plotcnt++,

pct_50=Col Quantile( If(excluded(rowstate(Row())),., column(plot_param[plotcnt])), 0.5);

test_list[plotcnt]=pct_50;

);

[#5]

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
plot_param={
	height,
	weight};
test_list={};

for(plotcnt=1, plotcnt<=nitems(plot_param), plotcnt++,

	Eval (Substitute (Expr(pct_50=Col Quantile( If(excluded(rowstate(Row())),., _col_), 0.5)), Expr(_col_), column(plot_param[plotcnt])));
	test_list[plotcnt]=pct_50;
);

[#6]

Due to a typo, I just noticed that Eval (substitute()) is enough, even if you substitute 1 with 1:

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
plot_param = {height, weight};
test_list = {};
For( plotcnt = 1, plotcnt <= N Items( plot_param ), plotcnt++, 

	pct_50 = Eval( Substitute( Expr( Col Quantile( If( Excluded( Row State( Row() ) ), ., Column( plot_param[plotcnt] ) ), 0.5 ) ), 1, 1 ) );
	test_list[plotcnt] = pct_50;
);
show(test_list);