mode function question

[#1]

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?

BR

Christer Ekblad

[francisvandergr]

Can you show your example a qvw ?

[#3]

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.

[#4]

sry,Here is the attachment

[#5]

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;

[#6]

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.

BR

Christer

[#7]

anyone?

[#8]

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

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

[#9]

i'm having the same problem.

Regards