How to calculate top 100 in obiee 11g analytics report?

ARIVEMULA KAVYA KUMARI, LERA TECHNOLOGY PVT LTD

Hi,

I am creting analytics report in obiee 11g.

I have 5 columns branch, district, outstanding balance, loan status, loantype.

I want to display the data of above 5 columns in the analytics report..

For this report, i want  to add custom rows..

My requiremt is, I want to dispaly the top 100 outstanding balances, total outstanding balance

and i want to add custom calculation like (total outstanding balance/ top 100 outstanding balance)*100

Please help me,

I have tried, i am not getting .

Please help me, How to calculate these?..

Thanks & Regards,

A.kavya

choracy69

Hi,

Did you tried to apply filter on measure column: IS IN TOP and there set: 100? Then you will see top 100 outstanding balances.

And in custom calculation maybe you can do so:

(max(RSUM(outstanding balance)) / outstanding balance)*100

max(RSUM(outstanding balance)) - maybe this is your total.

ARIVEMULA KAVYA KUMARI, LERA TECHNOLOGY PVT LTD

Hi,

I have tried..

I have kept filter on that column.. But

I am getting same results for total balance & top 100 total balance..

Please help me..

Thanks & Regards,

A.kavya

choracy69

Hm,

Maybe to calculate total balance and top 100 total balance you can use:

top balance

MAX(RSUM("outstanding balance"))

top 100 balance

MAX(RSUM(CASE WHEN RANK("outstanding balance")<101 THEN "outstanding balance" ELSE 0 END))

Such calculation to calculate the maximum value of the ranking of the top 100 - thats yout TOP 100 TOTAL.

If you then set the filter for column: is in the top 100, it changes only the view but the formula will count well.

That zour formule: MAX(RSUM("outstanding balance")) / (MAX(RSUM(CASE WHEN RANK("outstanding balance")<101 THEN "outstanding balance" ELSE 0 END)))*100

or for each record from top 100:

MAX(RSUM("outstanding balance")) / (CASE WHEN RANK("outstanding balance")<101 THEN "outstanding balance" ELSE 0 END)*100

Please check and let me know if it works correctly.

cesar.advincula.o

Yes,

1 column: outstanding balance

2 column: sum of the rank 100

case when rank(outstading balance) < 101 then outstaibd balance else 0 end

3.the column that you want to get:

Total outstanding / (case when rank(outstaibd balance) < 101 then outstaibd balance else 0 end) *100

Please, check in your formula your "aggregation rule" with SUM. That all.

Kind regards,

ARIVEMULA KAVYA KUMARI, LERA TECHNOLOGY PVT LTD

Hi,

as you said, i have tried.. But i didnt get correct data..

I used unions also.. i didnt get correct. I am getting same results for total outstanding bal, total top 100 outsatnding bal, and  %ge share from npl.

please see the below screen shot.

Please help me

Thanks in advance,

A.kavya

cesar.advincula.o

Hello Kayva,

First we have to define, the rank, do you want, ask yourself, do you want rank by period, branch etc, so you have to use "RANK ( BY DIMENSIONS)".

I did a sample, with the similar columns that you use, please follow up.

I did 4 criterias using by "UNION" (not "UNION ALL", not "INTERSTECT", not "MINUS"):

And my results are:

Check it out:

1st  criteria:  -I have the sum 187 157 538.57 as the sum of the top 10 projects, by the Company(Empresa) and by the Period (MesAñoDocum)

2do criteria: -Check the second Row "SumaTotalProyectos" which is the Total Sum of the Company in Ene-2015  (January 2015) for all projects. Which is 211 249 318.42

3rd criteria:  -Check the 187 157 538.57 which is the sum of the top 10 projects

4rd criteria:  -Which is the percentage of it,  187 157 538.57 / 211 249 318.42 = 88.60%

How do you setting it?

1. Lets check Criteria 1: the sum of the top 10 projects

Check the RANK Filter

2. Lets check Criteria 2: Total Sum of the Company

3. Lets check Criteria 3: Sum Ranking

4. Lets check Criteria 4: the Percentage

Note:

As you see i have two projects "columns" i need it to get the sum of ranking, for example, you have to do a ranking by branch and get the same, you have to put twice this column in your criterias, and after it you could exclude.

Kind Regards,