Analytics Welcome to the Teradata Forums. Read the guidelines on posting. Email notifications and RSS feeds are available, and also a chronological list of all forum activity.
 Print All Forums Analytics jainayush007 50 posts Joined 03/11 03 May 2011 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 Jimm 298 posts Joined 09/07 06 May 2011 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. jainayush007 50 posts Joined 03/11 08 May 2011 Seems like EXCEL performs a median at least for 50 percentile. ;) You must sign in to leave a comment. Active Posters