1 person found this helpful
This report is in Oracle BI Apps - HR - Compensation ... first let's look at what NTILE function is and does:
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.
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.
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.
Thanks Thomas, your explanation clarifies my queries;-)