Percentiles in JMP different from other programs, how to get the same in JMP?

Georg

Dear community,

we found that JMP calculates the percentiles differently from other programs, how can I get the same value in JMP?

Other programs are e.g. Oracle SQL and Excel (PERCENTILE.INC)

See some references and example in script below.

Thanks and BR

 

 

names default to here(1);

// JMP calculates other Quantiles than Python/Excel
// how to get same values in JMP?

// see:
// web("https://community.jmp.com/t5/Discussions/Quantiles-result-from-JMP-are-different-from-Python/m-p/762983");
// perhaps JMP equal to PERCENTILE.EXC in Excel, and not to PERCENTILE.INC

// Percentile Calculation
// web("https://community.jmp.com/t5/Discussions/Quantile-Percentile-Calculation/m-p/327198");

data = [10.56 10.85 11.08 11.57 11.81 12.27 12.76];

Q80_oracle = 12.178;
// as excel as well

Q80_jmp = Quantile( 0.8, data );
// 12.466

// how can I get same values as in other programs?

Georg

Dear all, 

thanks for the input. The reason why we need is "customer expectation", i.e. the addressee expects a certain way to calculate.

 

Meanwhile I found a way with a python workaround, see script below.

Thanks to all and best regards,

 

New Table( "Percentile_Cont_Example",
	Add Rows( 7 ),
	Compress File When Saved( 1 ),
	New Table Variable( "SQL",
		"with val_tab as
( 
select 1 AS lot, 11.08 as value FROM dual
union  select 1 AS lot, 12.76  as value FROM dual
union  select  1 AS lot,10.85 as value FROM dual
union  select 1 AS lot, 11.57 as value FROM dual
union   SELECT 1 AS lot, 10.56 as value FROM dual
union select 1 AS lot,  12.27 as value FROM dual
union  select 1 AS lot,11.81 as value FROM dual
)
SELECT
val_tab.lot
, val_tab.value
, percentile_cont(0.8) WITHIN GROUP (ORDER BY val_tab.value) OVER (PARTITION BY val_tab.lot) quant80_database
from 
val_tab"
	),
	New Column( "LOT",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [1, 1, 1, 1, 1, 1, 1] ),
		Set Display Width( 60 )
	),
	New Column( "VALUE",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [10.56, 10.85, 11.08, 11.57, 11.81, 12.27, 12.76] ),
		Set Display Width( 60 )
	),
	New Column( "QUANT80_DATABASE",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values(
			[12.178000000000001, 12.178000000000001, 12.178000000000001,
			12.178000000000001, 12.178000000000001, 12.178000000000001,
			12.178000000000001]
		),
		Set Display Width( 334 )
	),
	New Column( "Quantile80_JMP",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula( Col Quantile( :VALUE, 0.80000000000000004 ) ),
		Set Display Width( 141 )
	),
	New Column( "Quantile80_Python",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula(
			data = (As Column( :VALUE ) << get values)[
			Loc( As Column( :LOT ) << get values, :LOT )];
			Python Execute(
				{data},
				{result},
				"import numpy as np
result = np.quantile(data, 0.8)
"
			);
			result;
		),
		Set Selected,
		Set Display Width( 292 )
	)
)