How calculate Mode in OBIEE 11g? — Oracle Analytics

Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

How calculate Mode in OBIEE 11g?

Received Response
32
Views
3
Comments
Anirban1
Anirban1 Rank 4 - Community Specialist
edited Aug 13, 2024 7:04PM in Oracle Analytics Cloud and Server

Hi,

I have requirement from client to calculate median and mode and show that in a report. Median I am able to calculate as it is available in RPD but I am not able to get anything like MODE to calculate the same.

Please let me know if there is any way to calculate mode in OBIEE 11g.

Thanks

Anirban

Answers

  • choracy69
    choracy69 Rank 6 - Analytics Lead

    Hi,

    I do not think that as a 11g was built function mode. It is possible that the version 12c are added statistical functions.

    However, perhaps you can work around this by a simple calculation on the analysis.

    If you have, say, two columns: suppliers and a number of repeating them.


    Suppliers || Measure Column

    A     || 1

    B     || 2

    C     || 3

    D     || 1

    E     || 1

    F     || 2


    You can create an additional column of pulling the maximum number of records duplicate values.

    Example formule:

    MAX(RCOUNT(1 BY "Measure Column") BY "Measure Column")


  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    if you had:  1,2,3,4,5,5,5,6,7,8,9 --- mode would be 5 (it has the highest number of occurrences).

    Looks like you might need to have a separate (logical or physical) table that has the fact grain keys, the value you want the mode for and a 1 dummy column to sum, then you select the max(sum(1)) by the value-as-attribute  ... I think you can do it, but depending on the size of your data this could be very inefficient.  If you have a lot of data, you might want to implement with a physical table over a purely logical approach.

  • Anirban1
    Anirban1 Rank 4 - Community Specialist

    Hi Choracy,

    Thank you very much for your reply, it is working perfectly.