topic AW:Re: count on mode function in QlikView

count on mode function

rbecher — Mon, 05 Oct 2009 21:17:43 GMT

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

count on mode function

Mon, 05 Oct 2009 22:05:48 GMT

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.

AW:Re: count on mode function

rbecher — Mon, 05 Oct 2009 22:26:02 GMT

Hi Miguel,

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

- Ralf

AW:Re: count on mode function

rbecher — Mon, 05 Oct 2009 22:59:12 GMT

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

AW:Re: count on mode function

johnw — Tue, 06 Oct 2009 00:32:59 GMT

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

AW:Re: count on mode function

rbecher — Tue, 06 Oct 2009 01:37:09 GMT

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

AW:Re: count on mode function

johnw — Tue, 06 Oct 2009 03:09:43 GMT

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.

AW:Re: AW:Re: count on mode function

rbecher — Tue, 06 Oct 2009 03:44:41 GMT

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

- Ralf

AW:Re: AW:Re: count on mode function

johnw — Tue, 06 Oct 2009 04:51:39 GMT

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.

count on mode function

Tue, 06 Oct 2009 14:20:44 GMT

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.

AW:Re: count on mode function

rbecher — Tue, 06 Oct 2009 15:47:58 GMT

Hi Miguel,

this is a interesting approach with inputfield. In my case it would makes no sense because the mode is changing from selection and nobody would input that data.

I had also some thoughts about to calculate the mode in the script and then having the result in another table for comparison. But this also would be no dynamically solution...

- Ralf

AW:Re: count on mode function

Tue, 06 Oct 2009 16:00:36 GMT

Hi Ralf,

I don't understand, why did you say "nobody would input that data"?, because, the data in the input field are calculated trough a macro and it will be always calculated after triggers like open document, changing selection, etc, so, nobody have to input nothing, all is always dinamic.

I made some tests after send the "solution" changing values and it always works.

Sorry, I assumed this would help.

Best regards.

AW:Re: AW:Re: count on mode function

rbecher — Tue, 06 Oct 2009 20:12:46 GMT

Hi Miguel,

sorry I got it wrong in the first place. I have to go more deeper into the example. But I'm concerned about large datasets with macro execution...

- Ralf

AW:Re: AW:Re: count on mode function

Tue, 06 Oct 2009 20:30:29 GMT

Hi Ralf,

I don't know what is the penality for large datasets (maybe you could try and share the result with us jajaja) but I don't have any more ideas for solving your problem.

good luck.

Best regards.

AW:Re: AW:Re: AW:Re: count on mode function

rbecher — Thu, 08 Oct 2009 04:27:41 GMT

I really wonder wha this is an issue because these values are available in listboxes!

- Ralf