Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to get the main occurrence

Hello,

I have a table like this:

ID          Result

01          blue

02          blue

03          red

04          blue

05          red

06          blue

I want an expression that gives me the majority of the results, in that case it should return "blue".

For the moment, when I try "=Result" it returns "-" because there is different possible value...

How can I do this?

Thank you!

1 Solution

Accepted Solutions
Not applicable
Author

Hi Anne,

maybe try using mode

Mode(Origine_Code)

hope that helps

Joe

View solution in original post

7 Replies
arulsettu
Master III
Master III

hi

try this

=MinString(Result)

rubenmarin1

Hi anne-celine, you can get that with:

=Aggr(If(Rank(TOTAL Count(Result))=1, Result), Result)

simenkg
Specialist
Specialist

FirstSortedValue(Result,-Aggr(Count(ID),Result))

Not applicable
Author

My example was maybe not explicit enough. It's more something like:


ID           Job          Level      Origine_code

01          Seller          1              100

02          Seller          2              200

03          Seller          2              200

04          Seller          2              100

05          Seller          2              200

06          Manager      3              100

07          Manager      3              200

08          Manager      3              100

09          Manager      3              100

10          Director       4              200


MinString(Result) doesn't work for this.

The two others expressions are not completely working...

When I chose the job "Seller", i would like to know the most used origine for each level (Seller level 1: 100 / Seller level 2: 200, even if there is an exception for one of them which comes from 100). For the job Manager: Main origine for Manager level 3 = 100)...


Thank you for your help

Not applicable
Author

Hi Anne,

maybe try using mode

Mode(Origine_Code)

hope that helps

Joe

Not applicable
Author

Thank you, that's exactly what I was looking for!

Not applicable
Author

No problem glad to help.

the one thing to note with mode is it will return you a null when you have multiple equally weighted results. So you might want to combine it into a slightly more complex expression to handle those occurrences.