topic Re: Distinct Counts based on does not equal in QlikView

Distinct Counts based on does not equal

aliyoung92 — Wed, 25 Nov 2020 16:16:04 GMT

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

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

Re: Distinct Counts based on does not equal

its_anandrjs — Wed, 06 Dec 2017 15:30:08 GMT

Let me know that Score is a field or expression.

Re: Distinct Counts based on does not equal

aliyoung92 — Wed, 06 Dec 2017 15:31:46 GMT

Field

Re: Distinct Counts based on does not equal

Ralf_Heukäufer — Wed, 06 Dec 2017 15:32:31 GMT

Try

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

Re: Distinct Counts based on does not equal

its_anandrjs — Wed, 06 Dec 2017 15:34:43 GMT

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

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

Re: Distinct Counts based on does not equal

sunny_talwar — Wed, 06 Dec 2017 15:37:38 GMT

Try this

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

Re: Distinct Counts based on does not equal

aliyoung92 — Wed, 06 Dec 2017 15:38:46 GMT

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

Not too sure why

Re: Distinct Counts based on does not equal

aliyoung92 — Wed, 06 Dec 2017 15:43:50 GMT

This seems to work, thank you.

Can i ask what 'e' is?

I've never come across this?

Re: Distinct Counts based on does not equal

sunny_talwar — Wed, 06 Dec 2017 15:48:54 GMT

Check here

P() & E() and where do you use them?