count on mode function

[rbecher]

Hi,

I'm trying to count the occurrence of the most frequent values in a diagram like this:

count( {$ < Muster = {"=mode(Muster)"} >} Muster )

but the result is wrong. How is the right expression?

- Ralf

[#2]

Hi Ralf,

I think the correct sentence is:

count( {$ < Value = {$(#=mode(Value))} >} Value )

At least in my instance this has worked.

Remember if the Mode function founds more than one value, it will return NULL.

Best regards.

[rbecher]

Hi Miguel,

I've tried this and it doesn't work. The result is always zero (0).

- Ralf

[rbecher]

Miguel,

thx for the example. In my case I have a pivot table. I also tried with aggr function but the results are the same (wrong). Your mention about more than on value is not quite right. This is according to the only function.

- Ralf

[johnw]

If more than one value has the maximum number of occurrences, mode() will return NULL.

[rbecher]

I did extend the example to show the wrong calculation.

The frequency for Dim=B should be 2 because the value 4 occures two times...

- Ralf

[johnw]

Well, I don't have a solution, but I can tell you what's wrong. The $(#=mode(Value)) is analyzed ONCE for the entire chart, so returns 2. 2 occurs twice in A, once in B, and twice in C. That's why you're seeing the results you're seeing.

[rbecher]

Okay thx John, I've got it. But what is with my first wrong version (see example attachment).

- Ralf

[johnw]

Well, "" means you're specifying a search mask, and "=..." means you're specifying an advanced search. The expression in an advanced search should return 0 (false) for rows that don't match, and non-0 (true) for rows that do match. Mode(Value) returns non-0 for all values but 0, for which it returns 0. So all non-0 values match your advanced search. So what you've done is tell QlikView to return a count of all non-0 values.

Again, that doesn't tell you how to fix it, but that's what went wrong.

This seems like it should be easy, but I haven't thought of how to make it work.

[#10]

Hi everybody,

I've found a solution using Input_fields (I've changed the data source because I don't know how use input fields with Inline tables), your problem remember me another case in the community, I've publicated an example "How to calculate an expression which includes an aggregation within another aggregation using Inputfields" a few days ago.

Basicly, I use an input_fields to calculate and keep the aggregate function, in this case the mode, after this I use the value to do the count function.

What are the black points?:

• You need 2 fields of selection and they must be hidden
• This solution could be a little slow depends on the record numbers
• The macro for calculating the input_field must be called in a few events (see the Macros section on the Document properties)

I've attached an example.

I hope this help you.

Best regards.