OBIEE: December 2011

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.

Sort the data from low to high;

Count the number of values (n);

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

Sorted data: 18, 33, 58, 67, 73, 93, 147

There are n=7 observations.

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 !!!

Look at the problem first -

I am using a link object and used a Go URL to display a report.

Let us click on the link.

Now to go back to the previous page , I will have to click the "Back" button of the Browser.

Workaround :- Go to the original report and add a static text view and add the code as shown below. I have added SPAN tags as well so that the return link will appear along with other report links in the dashboard.
Note - You will have to adjust the position parameters (left, top or right ) according to your setup.

<SPAN STYLE="position: relative; left:-4.5px;top:16.1px;">
<A href="javascript:history.back()">- Return</A>
</SPAN>

Now go back agian to the dashboard page.

Click again -

Have a look at the "Return" link and now you can go back :-)

(Note: - If at all you don't want the "return" link then there is no need to add the static text view in the original report -simply use "Open in a new window" option in the link object or Back button of the browser if you want the report to be opened on the same page. )

Have a nice day !

25th Percentile

Go URL with "Return" link