Nested Aggregation

[asinghal0412]

Hey Qlik Community,

I have 2 fields: GroupID, UserID

In 'GroupID'  value is {PMO_Users, IT_Users, ACT_Users}

Now a user can have access to multiple 'GroupID'

I want to create a measure for count(using set analysis) in which I display the count of userID which have access to only 'PMO_Users' groupID.

My code is :

count(distinct if([GroupID]='PMO_users' and count([GroupID]) = 1,[FPL UserID]))

The error: Nested Aggregation not allowed.

Can someone help me in correcting the code or provide some alternative solution to achieve the result?

If the question is unclear please let me know.

Thanks,

AS

[GaryGiles]

Something like this:

=count({$<[FPL UserID]={"=count(distinct [GroupID])=1"},[GroupID]='PMO_User'}>} distinct [FPL UserID])

[asinghal0412]

Dear Gary,

Thanks for your response, but, it does not give the solution. The count is zero.

IF I may ask, why are you storing count(distinct [GroupID])=1 in [FPL UserID]?

=count({$<[FPL UserID]={"=count(distinct [GroupID])=1"},[GroupID]='PMO_User'}>} distinct [FPL UserID])

best,

AS

[MayilVahanan]

Hi 

Try like below

count({$<[FPL UserID]={"=count({<[GroupID]={'PMO_User'}>}distinct [GroupID])=1"}>} distinct [FPL UserID])

or

Sum(Aggr(If(count({<[GroupID]={'PMO_User'}>}distinct [GroupID])=1, 1), FPLUserID))

[GaryGiles · Accepted solution]

I missed a { in the set analysis.  Try this:

=count({$<[FPL UserID]={"=count(distinct [GroupID])=1"},[GroupID]={'PMO_User'}>} distinct [FPL UserID])

The portion in set analysis [FPL UserID]={"=count(distinct [GroupID])=1"} causes Qlik to calculate count(distinct [GroupID] for every [FPL User ID] and comparing it to 1 gives me the [FPL UserID]s that only have only one [GroupID].  The next portion, [GroupID]={'PMO_User'} gives me the [FLP UserID]s with 'PMO_User'.

[asinghal0412]

Thanks, Gary this gives me the perfect result.

Have a great day!

AS

[asinghal0412]

Dear Mayil,

I tried your code, unfortunately, it returns the entire list of people if 'PMO_users' access, does not seem to implement the other condition.

I got the solution using Gary's code. Thanks again for your support!