Possible Aggr Function Help

[mccook]

Hi,

I have a table like below:

User	Role
Gavin	Audit
Tim	Audit
Harps	Audit
Gavin	Purchasing
Tim	Purchasing
Tim	Purchasing
Dean	Audit
Harps	Audit

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]

In your list box expression, use below

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

or

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

UPDATE : Please check enclosed file.

[alexandros17]

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]

Thanks,

Sorry, I ment text box.

[Gysbert_Wassenaar]

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

[mccook]

thanks,

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

[maxgro · Accepted solution]

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

[MK_QSL]

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

[MK_QSL]

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