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

[#1]

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?

[#2]

Hi,

I just tested your dataset in Excel, and I got a Q80 value in Excel = 12.466, exactly the same as JMP.

I cannot test in Oracle, but I noticed that in R, I get a Q80 value = 12.178. Moreover, the Q90 in R is 12.466. 

There must be an explanation on how the quantiles are calculated between these programs.

Best,

TS

[#3]

One more thing:

In R, there are 9 different ways to calculate quantiles. 

Method 6 returns the same Q80 as JMP and Excel

Method 7 returns the Q80 you found in Oracle:

Best,

TS

[#4]

In Excel there are two ways, 

PERCENTILE.INC and PERCENTILE.EXC.
My problem was, that I couldn't get the same value in JMP as customer with PERCENTILE.INC, so that workaround with python was needed.

[#5]

Hi @Georg : I'm curious; do you mind if I ask why it matters if they match or not?

[#6 · Accepted solution]

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

[#7]

How do I install numpy for JMP 18?  I have a separate installation of python 3.13.2.  I installed numpy for that version but still get this error when trying to run the above code

ModuleNotFoundError: Please install the Python package NumPy.
 at row 7

[#8]

Python Install Packages example from scripting index should work

Names Default To Here(1);
// install numpy and pandas packages
Python Install Packages("numpy pandas");

or you can use python side of JMP and use jpip

import jmp

import jmputils
# update to latest version of pip and setuptools then install numpy & pandas
jmputils.jpip('install --upgrade', 'pip setuptools')
jmputils.jpip('install', 'numpy pandas')

https://www.jmp.com/support/help/en/18.0/#page/jmp/install-a-python-package.shtml#

[#9]

Did you try

Python Install Packages( "numpy" );

[#10]

Thanks that worked.  Wasn't familiar with that command.