topic How to get the main occurrence in QlikView

How to get the main occurrence

Fri, 13 Feb 2015 10:25:13 GMT

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!

Re: How to get the main occurrence

arulsettu — Fri, 13 Feb 2015 11:07:17 GMT

try this

=MinString(Result)

Re: How to get the main occurrence

rubenmarin — Fri, 13 Feb 2015 11:12:28 GMT

Hi anne-celine, you can get that with:

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

Re: How to get the main occurrence

simenkg — Fri, 13 Feb 2015 11:35:37 GMT

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

Re: How to get the main occurrence

Fri, 13 Feb 2015 12:40:18 GMT

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

Re: How to get the main occurrence

Fri, 13 Feb 2015 12:50:06 GMT

Hi Anne,

maybe try using mode

Mode(Origine_Code)

hope that helps

Joe

Re: How to get the main occurrence

Fri, 13 Feb 2015 12:53:58 GMT

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

Re: How to get the main occurrence

Fri, 13 Feb 2015 13:03:57 GMT

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.