How calculate Mode in OBIEE 11g?

Question

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

Anirban1 · Answer

Hi Choracy,

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

Thomas Dodds · Answer

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.

choracy69 · Answer

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")