topic Re: Find maximum frequent number in QlikView

Find maximum frequent number

Wed, 30 Apr 2014 09:41:11 GMT

load * Inline
[Numbers
20
25
35
35
35
44
11
11
55
55
];

I have used =max(aggr((count(Numbers)),Numbers))

In text box I want 35 instead of 3. Because number 35 has
repeated 3 times.

Any Idea?

Regards

Vinay

Re: Find maximum frequent number

MK_QSL — Wed, 30 Apr 2014 09:42:29 GMT

May be something like this....

=Aggr(IF(COUNT(Numbers)=MAX(TOTAL Aggr(COUNT(Numbers),Numbers)),Numbers),Numbers)

=Aggr(IF(COUNT(Numbers)=MAX({1}TOTAL Aggr(COUNT({1}Numbers),Numbers)),Numbers),Numbers)

Re: Find maximum frequent number

CELAMBARASAN — Wed, 30 Apr 2014 09:50:31 GMT

Did you tried with the function called "Mode"

Mode(Numbers)

But it will give you null when morethan one value is in first place.

Like if you add one more 55 to your list, it will results you null value.

Re: Find maximum frequent number

Wed, 30 Apr 2014 09:52:26 GMT

Hi Vinay,

use the Count() Function.Refer the attached QVW File.

Re: Find maximum frequent number

Wed, 30 Apr 2014 09:57:41 GMT

Hi Manish,

Thanks for reply and its working.You are really a GURU.

Regards

Vinay

Re: Find maximum frequent number

anbu1984 — Wed, 30 Apr 2014 10:20:32 GMT

In Script

Load FirstSortedValue(Numbers,-Cnt);

Load Numbers,count(Numbers) As Cnt Group by Numbers;

load * Inline

[Numbers

];

Re: Find maximum frequent number

MK_QSL — Wed, 30 Apr 2014 12:51:15 GMT

Use this if you have more than one Numbers are appearing in TOP position...

=CONCAT(Aggr(IF(COUNT(Numbers)=MAX({1}TOTAL Aggr(COUNT({1}Numbers),Numbers)),Numbers),Numbers),',')

Re: Find maximum frequent number

rwunderlich — Wed, 30 Apr 2014 13:36:57 GMT

Celambarasan Adhimulam

The previous posted solutions also don't work if more then one value shares first rank. This expression will work for that case:

=FirstSortedValue(DISTINCT Numbers,-aggr(count(Numbers),Numbers))

-Rob

Re: Find maximum frequent number

MK_QSL — Wed, 30 Apr 2014 13:45:47 GMT

What about second solution given !!!

Rob, I tried a lot but it's not working.

If I want the answer 35 should be there even though if user select any Numbers...how can we achieve?

Re: Find maximum frequent number

rwunderlich — Wed, 30 Apr 2014 14:07:12 GMT

Manish,

Sorry, I missed your second solution. To ignore selections in my expression just add the {1}

=FirstSortedValue({1}DISTINCT Numbers,-aggr(count({1}Numbers),Numbers))

-Rob

Re: Find maximum frequent number

MK_QSL — Wed, 30 Apr 2014 14:10:47 GMT

Thanks Rob... ! You are a champion.!

Re: Find maximum frequent number

MK_QSL — Wed, 30 Apr 2014 14:12:47 GMT

One question, using my solution how can we get the same result with ignoring user's selection of Numbers !

Thanks in advance for your precious time !