topic mode function question in QlikView

mode function question

Sun, 06 Mar 2011 18:22:07 GMT

Hi!

I am using the mode function in order to find the most occurring value over a few dimensions (in a chart).

The problem is when different values are occurring the same amount of times, then null is retrieved.

What i want is then to have qlikview select the minimum value of the values that are occuring the same amount of times.

Any ideas?

Christer Ekblad

mode function question

francisvandergr — Sun, 06 Mar 2011 21:33:57 GMT

Can you show your example a qvw ?

mode function question

Mon, 07 Mar 2011 11:25:17 GMT

Hi, Here is the sample Data

ID	cal
1	34
1	24
1	24
1	34
2	15
2	14
2	14
2	14
2	15

So,In the attachment, first i m counting the occurence of each value,then in next load i m calucating the MODE as our wish.

mode function question

Mon, 07 Mar 2011 11:30:35 GMT

sry,Here is the attachment

mode function question

Mon, 07 Mar 2011 11:34:39 GMT

sry,

Unable to attch the file.

//Loading the Values
[MODE1]:
LOAD ID,
cal
FROM
[Mode value.xls]
(biff, embedded labels, table is Sheet1$);
//Counting the occurence of each value by grouping the ID and Cal
MODE2:
LOAD ID,
count(ID) as occurence,
only(cal) as [cal of max]
resident [MODE1] group by ID,cal;

//Here calculating mode
left join
LOAD ID,
max(occurence) as [Max occurence],
MIN([cal of max]) as Value
resident MODE2 group by ID;

mode function question

Mon, 07 Mar 2011 13:08:16 GMT

Hi!

Here is an example. I would like to do this in a graph (table) and not in the script if possible.

The problem is that for Sweden, QV cannot display the mode(Quantity) beacuse 49 and 50 occurrs the same amount of times. What I then would like is QV to show 49. Hence, the minimum of the two.

Christer

mode function question

Fri, 11 Mar 2011 10:01:37 GMT

anyone?

mode function question

Thu, 07 Jun 2012 19:55:46 GMT

Shot in the dark, but try this in an expression:

=if(isnull(mode(cal)) = -1,min(cal),mode(cal))

Re: mode function question

Wed, 16 Dec 2015 15:58:10 GMT

i'm having the same problem.

Regards