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
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
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
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;
);
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++,
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;
);
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);
@LatentDataTiger , feedback from JMP Support (TS-00139301)
Yes, this is a bug. The developers have verified it is a caching issue with Col Quantile [& Col Median] specifically. The other Col functions do not have the issue. Using Substitute makes a new expression with each iteration of the loop so that caching does not get used improperly. Thanks for reporting this and I will file a bug report.
So, under the line, it's not Substitute, but the protection via Eval(Expr()) which fixes the bug.
nice example from @Arn_Cota [adapted version] illustrating the effect:
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++,
// Without Exclude, returns correct values
pct_50_v0 = Col Quantile( Column( plot_param[plotcnt] ), 0.5 );
// With Exclude, returns the Quantile for height
pct_50_v1 = Col Quantile(
If( Excluded( Row State( Row() ) ),
.,
Column( plot_param[plotcnt] )
),
0.5
);
// With Eval and Substitute, returns correct values
pct_50_v2 = Eval(
Substitute(
Expr(
Col Quantile(
If( Excluded( Row State( Row() ) ),
.,
Column( plot_param[plotcnt] )
),
0.5
)
),
1, 1
)
);
// use Eval(Expr() to protect the expression against inappropriate caching
pct_50_v3 = Eval(Expr(Col Quantile(
If( Excluded( Row State( Row() ) ),
.,
Column( plot_param[plotcnt] )
),
0.5
)));
// Show different versions
Show(
plotcnt,
Column( plot_param[plotcnt] ),
pct_50_v0,
pct_50_v1,
pct_50_v2,
pct_50_v3
);
test_list[plotcnt] = pct_50_v3;
);
example/workaround added to Expression Handling in JMP: Tipps and Trapdoors