create a measure to count distinct fields

[asinghal0412]

Good morning Qlik Community,

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

I have a field name, 'GroupID' with 20 domain categories including 'All Users', 'Al Users', 'ACT users', 'ICT users', etc. Every employee has a unique id which is the field, 'User_ID'. 

Note: An employee can have access to multiple domains. 

For example:

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

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

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

User 4 has access to only - 'All 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 'User ID' that does not have 'All Users' domain access. (Neither single nor in the multiple access.) 

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

I hope the question was clear if not please let me know. 

Best Regards,

AS

[JordyWegman]

Hi Asinghal,

Try this:

Count( Distinct {$< GroupID -= {'ALL Users'}>} UserID )

The Count counts of course. The Distinct makes sure you only get unique users IDs and the '-=' excludes the GroupID with All Users.

Jordy

Climber

[asinghal0412]

Hi Jordy,

Thank you for your message, I tried the code that you shared but, it returns the wrong results. Maybe instead of count the User ID if you can share a measure to display the user ID of the mentioned condition, it would work too.

Best,
AS