Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 208 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 76 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
How to calculate top 100 in obiee 11g analytics report?
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
Answers
-
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.
0 -
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
0 -
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.
0 -
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,
0 -
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
0 -
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,
0