Nested Aggregation

[asinghal0412]

Hey Qlik Community,

I am attaching a code below.

count(distinct (if([FPL GroupID]='all_pmo_users' and count([FPL GroupID]) = 1,[FPL E-Mail])))

It says, I am unable to run the code as Qliksense does not allow Nested Aggregation, can anyone help me?

Thanks,

AS

[Dalton_Ruer]

I don't understand what you are trying to do. 

The FPL_GROUPID = 'all pmo users'  would evaluate each row but then you are adding a count function. The count for a field on a row by row basis will always be 1 which is why I'm lost on what you are trying to accomplish. 

Please share some sample data, and what you are trying to achieve.

[asinghal0412]

Hey Dalton,

Thanks for the response, actually my doubt was very straightforward and I saw a lot of nested aggregation issues, so I thought the fix will be very straightforward. But, please check the example below.

I am stuck while creating a measure and need your help.

I have a field name, 'FPL GroupID' with 20 domain categories including 'All PMO_Users', 'Al Users', 'ACT users', 'ICT users' (made up domains), etc. Every employee has a unique id which is the field, 'FPL email'. 

Note: An employee can have access to multiple domains. 

For example:

User1 has access to - 'All PMO Users', 'ACT users' domain, 

USer 2 has access to - 'All PMO Users', 'ICT users' domain, 

User 3 has access to - 'ACT Users', 'ICT users', 'AI users' domain,

User 4 has access to only - 'All  PMO Users' domain,

User 5 has access to only - 'ICT users' domain and so on.

I want to create a measure (Using set analysis in the front end) that returns the count of 'FPL email' that does not have 'All PMO_Users' domain access. (Neither single nor in the multiple access.) 

In the above example, the result should be 2 (User 3 and User 5)

IF you have any questions let me know!

Best,

Akash S.

[Dalton_Ruer]

I used this:

Count(distinct {<User -= { "=Count({<Group={'All PMO Users'}>} User) > 0" }>} User)

for this data set:

Data:
Load * Inline [
User, Group
User1, 'All PMO Users'
User1, 'ACT users domain'
User2, 'All PMO Users'
User2, 'ICT users domain'
User3, 'ACT Users'
User3, 'ICT users',
User3, 'ACT users domain'
User4, 'All PMO Users'
User5, 'ICT users domain'
User6, 'All users domain'
];

[asinghal0412]

Thanks for the solution Dalton but the code return all the UserIDs, does not work. Can you take a look at it?

AS