Hello,
I have analysis in OBIEE 11g. In column there are repeating numbers.
For example:
1
2
4
5
How to determine the most frequently occurring value/number in column?
Aggregate using COUNT and sort that counting column in descending order.
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?
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)
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.
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.
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.
Thank you, but I have one more question.How can I calculate COUNT for the same numbers in Col B?
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:
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!
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.
Learn how to deploy an AI Data Platform agent, register it to the AI Registry, and introduce it to business users through Agent Hub. This session covers how to prepare an agent for deployment, configure endpoint access and authentication, invoke a deployed agent, and monitor it after deployment. The session also introduces…
Can you please help resolve this error. WebLogic version is 12c. OAS is 6.4
So I have an action passing a value to another workbook. On the other workbook, I have an expression filter and a parameter I created and it does not seem to work. Any ideas. See the images
We know you can directly export workbooks and folders from within OAC, and we know you can migrate OAC content using the FDI Content bundle. However, it's unclear what settings or steps are required in order to ensure any calculations in "Shared Objects" still carry over. When making a .dva artifact export from OAC for…
The user icon in the upper-right corner of OAC (DV) is displaying more menu options for our non-admin users than we wish to display. We would like to restrict these to only "Profile" and "Sign Out" for regular users. Is there a way to customize these menu items? Especially if we can control this via security, that would be…
What is wrong with this code
