25th Percentile

Frankly before starting I had no idea about what the percentile was but in this post I have somehow tried to caculate or show the "25th percentile" ( Even don't know whether it is correct or not )


I must mention http://www.childrensmercy.org/stats/definitions/percentiles.htm from which I got some idea on percentile.


Background :

What is a percentile?

The pth percentile is a value so that roughly p% of the data are smaller and (100-p)% of the data are larger. Percentiles can be computed for ordinal, interval, or ratio data.
There are three steps for computig a percentile.

1. Sort the data from low to high;
2. Count the number of values (n);
3. Select the p*(n+1) observation.

Examples

The following data represents cotinine levels in saliva (nmol/l) after smoking. We want to compute the 50th percentile.

73, 58, 67, 93, 33, 18, 147

1. Sorted data: 18, 33, 58, 67, 73, 93, 147
2. There are n=7 observations.
3. Select 0.50*(7+1)=4th observation.

Therefore, the 50th percentile equals 67. Notice that there are three observations larger than 67 and three observations smaller than 67.

 Note - Compute the 10th percentile. 
          0.10*(7+1)=0.8 ==> equivalent to 1 , so select the first element in the
           ascending list , that is 18
         
Now let us go back to OBIEE.


At first I have taken a column called Calender.Year as shown below.

By looking at the formula and example I conclude -
Data is sorted in asc order, there are 5 elemnts in total. 


To find the observation at the 25th percentile - I have to use below formula- 
(25/100) * ( Total number of elements in the column + 1 )
In terms of OBIEE I may write -


ROUND(((0.25))*(MAX(RCOUNT("TIMES"."CALENDAR_YEAR"))+1),0)


Take one more Calender Year column in the report, rename properly and apply this formula.

So the element with position number 2 is the 25th Percentile of this Calender Year score that is year 1999.


Let us try showing the element with position number 2.


Take another Calender Year column and write the below formula -


CASE WHEN (RCOUNT("TIMES"."CALENDAR_YEAR")=(ROUND(((0.25))*(MAX(RCOUNT("TIMES"."CALENDAR_YEAR"))+1),0))) THEN "TIMES"."CALENDAR_YEAR" ELSE
0 END


Look at the report.

Have a nice day !!!