topic Re: Percentiles in JMP different from other programs, how to get the same in JMP? in Discussions

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

Georg — Tue, 18 Mar 2025 13:42:49 GMT

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?

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

Thierry_S — Tue, 18 Mar 2025 16:25:26 GMT

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,

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

Thierry_S — Tue, 18 Mar 2025 16:31:46 GMT

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,

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

MRB3855 — Tue, 18 Mar 2025 16:42:51 GMT

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

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

Georg — Tue, 18 Mar 2025 17:02:16 GMT

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

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

pmroz — Tue, 18 Mar 2025 19:01:46 GMT

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

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

jthi — Tue, 18 Mar 2025 19:47:48 GMT

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#

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

mmarchandFSLR — Tue, 18 Mar 2025 19:49:07 GMT

Did you try

Python Install Packages( "numpy" );

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

pmroz — Tue, 18 Mar 2025 19:59:20 GMT

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

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

Georg — Wed, 19 Mar 2025 07:36:53 GMT

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.