topic Re: Problem with min and max count using set analysis in QlikView

Problem with min and max count using set analysis

Thu, 03 Apr 2014 21:30:24 GMT

Can anyone help?

I'm trying to display the min count and also the max count of items in column B on any one day. I have tried the aggr function and group by in script but to no avail.

Date B

01/01/2014 ABC

01/01/2014 BDC

01/01/2014 DEF

02/01/2014 CAD

02/01/2014 ABC

03/01/2014 ABC

The result that I'm after is min = 1 and max = 3

I'm sure it must be something really simple that I'm missing here?

Any help would be much appreciated.

Thanks

Re: Problem with min and max count using set analysis

its_anandrjs — Thu, 03 Apr 2014 21:38:39 GMT

To find min = 1 and max = 3 on column B

freq:

LOAD * Inline

[

Date, B

01/01/2014, ABC

01/01/2014, BDC

01/01/2014, DEF

02/01/2014, CAD

02/01/2014, ABC

03/01/2014, ABC

];

NoConcatenate

MaxMinFind:

LOAD

B as Bid,

Count(B) as Freq

Resident freq

Group By B

Order by B asc;

Let vMax = Peek('Freq',0,'MaxMinFind');

Let vMin = Peek('Freq',-1,'MaxMinFind');

DROP Table MaxMinFind;

And plot two text box and display Max and Min values like

=vMin and =vMax

Re: Problem with min and max count using set analysis

chiru_thota — Thu, 03 Apr 2014 21:45:21 GMT

You can do it with the help of variables.

See attached QVW.

Re: Problem with min and max count using set analysis

maxgro — Thu, 03 Apr 2014 22:36:35 GMT

in a text box

'Min = ' & min(aggr(count(distinct B),Date))

& chr(10) &

'Max = ' & max(aggr(count(distinct B),Date))

Re: Problem with min and max count using set analysis

Fri, 04 Apr 2014 09:56:08 GMT

This worked well. Thank you

Re: Problem with min and max count using set analysis

Fri, 04 Apr 2014 09:56:48 GMT

This works well - thank you for your help