topic Re: Possible Aggr Function Help in QlikView

Possible Aggr Function Help

mccook — Fri, 14 Mar 2014 15:53:07 GMT

Hi,

I have a table like below:

I want to display in a text box the number of Users who have more than one role. e.g. 2

I've been playing around with the aggr function and can't seem to get it to work (never used aggr before).

Cheers,

Dean

MK_QSL — Fri, 14 Mar 2014 15:55:43 GMT

In your list box expression, use below

=IF(AGGR(COUNT(Role),User)>=2,User)

=IF(AGGR(COUNT(Distinct Role),User)>=2,User)

UPDATE : Please check enclosed file.

alexandros17 — Fri, 14 Mar 2014 15:58:26 GMT

In your list box at the end of fields list there is "Expression" voice, select it and write

=if(Aggr(count(DISTINCT Role),User)>1, User, Null())

mccook — Fri, 14 Mar 2014 16:01:26 GMT

Thanks,

Sorry, I ment text box.

Gysbert_Wassenaar — Fri, 14 Mar 2014 16:02:08 GMT

You can use this as expression for the the text box: =count({<User={'=count(distinct Role)>1'}>}distinct User)

mccook — Fri, 14 Mar 2014 16:02:39 GMT

thanks,

Sorry I ment text box. Although that is very useful.

maxgro — Fri, 14 Mar 2014 16:03:49 GMT

=sum(if( aggr(count(distinct Role),User)>1, 1,0))

MK_QSL — Fri, 14 Mar 2014 16:06:06 GMT

=Concat({<User = {"=COUNT(Distinct Role)>=2"}>}Distinct User,', ')

MK_QSL — Fri, 14 Mar 2014 16:06:29 GMT

=Concat({<User = {"=COUNT(Distinct Role)>=2"}>}Distinct User,', ')