Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
aliyoung92
Contributor III
Contributor III

Distinct Counts based on does not equal

I have an issue where  I need to do a count of some unique values where the row has been duplicated by multiple scores.

I.e.

Username   Score

JD                4

JD                3

JD                2

JD                1

AB                1

AB                1

AB                1

AB                1

I want to do a count where only counts Usernames where the Username doesn't have a score of either 4,3,2. Basically so we can see the under performing Users.

So based on above data I'd want the count to be 1 (AB as has no high scores)

I'e tried      =Count({<Score -= {4,3,2}>} DISTINCT Username}     But does not work.

Thanks in advance

1 Solution

Accepted Solutions
sunny_talwar

Try this

Count({<Username = e({<Score = {2, 3, 4}>} Username)>} DISTINCT Username)


Capture.PNG

View solution in original post

8 Replies
its_anandrjs

Let me know that Score is a field or expression.

aliyoung92
Contributor III
Contributor III
Author

Field

Ralf_Heukäufer
Partner - Creator III
Partner - Creator III

Try

count(distinct if(Score<>2 and Score<>3 and Score<>4,Username))

its_anandrjs

Are you sure this expression not working close the bracket properly and check

=Count({<Score -= {4,3,2}>} DISTINCT Username )

sunny_talwar

Try this

Count({<Username = e({<Score = {2, 3, 4}>} Username)>} DISTINCT Username)


Capture.PNG

aliyoung92
Contributor III
Contributor III
Author

This has almost got me there but it is counting 34 when there should only be 33.

Not too sure why

aliyoung92
Contributor III
Contributor III
Author

This seems to work, thank you.

Can i ask what 'e' is?

I've never come across this?