I had a strange issue dealing with obiee rank function.My issues is i have a rank function which is based on attendance for a theatre:Ex:Rank(Attendance by Theater).The issue is that for two movies in a theater i have same attendance and i want to show top 3 movies for that theater.
Theatre Movie Attenadance Rank Starts
1 A 14 1 13
2 B 10 2 12
3 C 5 3 6.
4 D 5 3 5
Now my requirement whenever there is a same attendance i want check the rank based in starts.so that it can give different rank.How can i achieve this in OBIEE.
Any help is appreciated.
There is no direct way to do this. I can think of 2 approaches
1. Give a weight factor to the time in decimal value. The earliest time will have lowest value
earliest time will be 0.1, next time will be 0.11, next will be 0.111, next time will be 0.1111 and so on.
now divide attendance by this number. what ever result comes use it for ranking.
How this works - Attendance is the first order of ranking, attendance being same we need to rank based on time.
weight= Attendance/weight factor will put higher attendance automatically in higher order. if values are same, the earliest time will have more weight.
2. you need to do an ETL job/DB view. where you will first sort descending by Attendance and sort time as ascending in that order. Then insert a new column value starting with 1 and increment by 1
both can work.
mark if helps
Use two requests -
Request one based on Attendance and Request 2 based on Starts. use the EVALUATE function and native DB analytic Fn - DENSE_RANK as shown below.
EVALUATE('dense_rank() over(partition by %1 order by %2 )', <Theatre>, <Attendance / Starts> ).
You can use these requests in Dashboard and display them in separate sections based on the conditional request(#3) . Request 3 check for attendance for movies in the same theatre to be same.
Please let me know what you find out.