The most frequently occurring value - OBIEE 11g

4274543

Hello,

I have analysis in OBIEE 11g. In column there are repeating numbers.

For example:

1

1

2

2

4

4

4

4

5

How to determine the most frequently occurring value/number in column?

Gianni Ceresa

Aggregate using COUNT and sort that counting column in descending order.

4274543

Thank you for your answer, but that's not what I meant. The expected result for my example above

is "4" because number 4 has the highest repeatability.

Another example:

Maybe someone has right formula or better idea?

Jerry Casey

You just need to take Gianni's answer and apply the details that you didn't include in your original post.

Filter Column B on RANK(COUNT( "Col B" by "Col A"))=1 or TOPN(COUNT( "Col B" by "Col A"),1)

[Deleted User]

f0bcd0d0-f695-4dad-b1c8-311385c7b19b wrote:Thank you for your answer, but that's not what I meant.

Nobody here lives inside your head, so unless we develop the ability to read minds, it is impossible to give you an answer that will satisfy you if you do not provide full and correct details.

4274543

Ok, I'm sorry that my question was not clear.

When I used the above formula I received:

What am I doing wrong?

In this case, expected result is 21, because occurs most times.

Thank you for all the answers.

Jerry Casey

COUNT is an aggregate functions applied to Col B, so if you include Col B in the result, the Count of any one row will be 1.

Remove Col B from the query to see the aggregate result. Refer to the field name of Col B in the formula, only.

4274543

Thank you, but I have one more question.

How can I calculate COUNT for the same numbers in Col B?

Jerry Casey

You are showing accurate counts in that example.

To address your original question:

If I have data that looks like this:

Adding a TOPN column will serve as a filter on the query, and return only the rows that have the highest count:

4274543

Yes, of course, but I needn't the highest count, only the highest frequency.

I don't know how to create this column.

This is an example from Excel:

I want to get column (in Obiee):

and next I would like to determine the most frequently occurring value:

This is my expected result.

It is possible to do it?

Thank you!

Jerry Casey

That is exactly what I showed you except my original had an extra dimension. My "Count(Crash ID)" column is the frequency of occurrence of "Type".

After adding the TOPN column, the COUNT column is showing the max frequency of occurrence of Type by DOM.

Maybe this is clearer:

I don't include a column for "Crash ID" itself (your "Col B"), because then there would be a row for each one, and the aggregate functions would only be working on one row at a time.  If I exclude the TOPN colum from the view, I get this:

"Count(Crash ID)" is the maximum frequency of occurrence of crashes by type and day of month.