2 Replies Latest reply: Oct 23, 2013 1:07 PM by 861796 RSS

    Rank Issue

    user13001889

      Hi Gurus,

       

      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.

       

      Thanks!!!!

        • 1. Re: Rank Issue
          user10615659

          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

          • 2. Re: Rank Issue
            861796

            Hello there,

            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.

             

            Regards.