Percentile on MS Excel vs Teradata

Analytics
Enthusiast

Percentile on MS Excel vs Teradata

Hello,

My client performs percentile calculations using Percentile fuction on MS EXCEL. I am to get the same using Teradata.

I tried both Qunatile and Percent_Rank and none of them fetches the same result.

Here is an example:-

Value set:-

1034
1034
1034
1034
1034
1034
1034
1034
1137
1266
1273
1294
1294
1295
1305
1327
1327
1328
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1329
1335
1335
1341
1341
1341
1341
1341
1341
1362
1363
1371
1371
1371
1377
1377
1377
1377
1377
1377
1383
1391
1391
1391
1391
1391
1391
1391
1392
1392
1395
1395
1395
1395
1396
1399
1399
1411
1424
1431
1431
1434
1440
1446
1451
1451
1493
1551

On excel:- =PERCENTILE(D:D,0.5) =1329
On Teradata:-

SEL VALUE, PERCENT_RANK() OVER(ORDER BY VALUE) PER_VAL
FROM TBL ORDER BY PER_VAL;

VALUE PER_VAL
1329 0.155172
1335 0.603448

Any ideas how do I handle this?

Regards,
Ayush Jain
2 REPLIES
Enthusiast

Re: Percentile on MS Excel vs Teradata

Select Max(Vals)
From
(SEL "Value", PERCENT_RANK() OVER(ORDER BY "Value") PER_VAL
FROM T1) As A
Where Per_Val LE 0.5
;

I suggest you try with a couple more examples, but looks OK to me. Teradata and Excel use the same ranking methodology (ie not Dense_Rank), so results should be the same by this method.
Enthusiast

Re: Percentile on MS Excel vs Teradata

Seems like EXCEL performs a median at least for 50 percentile. ;)