4 Replies Latest reply on Oct 12, 2015 4:18 PM by Saro

BI percentile Calculation

Hi friends,

I was bit confused with the percentile calculation and will explain my scenario. I have the below report

In the above im not sure how does the average annual base pay is calculated. i can see the formula substituted for that column like below

AVG("Salary Facts"."Total Annual Base Salary" by NTILE("Salary Facts"."Total Annual Base Salary", 2))

For the above two row the Average annual base pay is of the below value with the above formula

144908.05

588320.46

But im not sure how does this formula calculates those respective value. Kindly clarify this calculation friends.

Regards,

Saro

• 1. Re: BI percentile Calculation

This report is in Oracle BI Apps - HR - Compensation ...  first let's look at what NTILE function is and does:

NTILE

This function determines the rank of a value in terms of a user-specified range. It returns integers to represent any range of ranks. In other words, the resulting sorted data set is broken into several tiles where there are roughly an equal number of values in each tile.

NTile with numTiles = 100 returns what is commonly called the "percentile" (with numbers ranging from 1 to 100, with 100 representing the high end of the sort). This value is different from the results of the Oracle BI PERCENTILE function, which conforms to what is called "percent rank" in SQL 92 and returns values from 0 to 1.

Back to your formula:

AVG("Salary Facts"."Total Annual Base Salary" by NTILE("Salary Facts"."Total Annual Base Salary", 2))  <-- OOTB formula from Oracle is numTiles = 5

So your values are (the resulting sorted data set) broken into 2 (several) tiles where there are roughly an equal number of values in each tile.  THEN there is an AVERAGE imposed on the values grouped by each tile.

My opinion:  unless your users understand the NTILE function and it's intent (with it being only 2 tiles) - this OOTB approach by Oracle doesn't serve you much information.

1 person found this helpful
• 2. Re: BI percentile Calculation

Hi Thomas,

Thanks for the reply, so it seems like Avg is calculated like(min annual base salary + max annual base salary) / 2

IF that's the above case then, im getting some mere difference.

Regards,

Saro

• 3. Re: BI percentile Calculation

No ... the Average for that NTILE ... it's not based on MAX or MIN ...

NTILE = 5 approximately puts 20% of the values, once sorted, in each group (TILE).  From there the AVG() function is attempting to find the average value in that 20% of values.  So if in total you had 10 original rows, 2 rows would be in each TILE ... at that point you'd have the average between only two values.  If you had 100 rows, then you'd be looking at the average in each of the 5 groups across 20 rows per group.

• 4. Re: BI percentile Calculation

Thanks Thomas, your explanation clarifies my queries;-)

Regards,

Saro