topic Re: Count Distinct ID where ID Occurs more than once in QlikView

Count Distinct ID where ID Occurs more than once

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

Hi All,

I am trying to create a chart from my data set where I am getting a count of distinct id's where the id occurs more than once.

Below is a look at a pivot chart i created using the data. Any idea?

Re: Count Distinct ID where ID Occurs more than once

sunny_talwar — Thu, 26 Apr 2018 14:54:20 GMT

May be this

Count({<ID = {"=Count(ID) > 1"}>}ID)

Re: Count Distinct ID where ID Occurs more than once

Anonymous — Thu, 26 Apr 2018 15:00:55 GMT

Hi stalwar1,

I have tried that formula but it doesnt seem to work for some reason.

Finbar

Re: Count Distinct ID where ID Occurs more than once

sunny_talwar — Thu, 26 Apr 2018 15:02:06 GMT

It may be confidential, but are you using set analysis between Count and ID which you have colored in Red? Are you using the same set analysis in the Search string?

Re: Count Distinct ID where ID Occurs more than once

Anonymous — Thu, 26 Apr 2018 15:06:41 GMT

I am doing a simple count(id) in the table above. So it is telling me how many times the unique policy id occurs in the table.

Re: Count Distinct ID where ID Occurs more than once

sunny_talwar — Thu, 26 Apr 2018 15:15:30 GMT

I was asking about this

Is the hidden part in Red a set analysis? or what?

Re: Count Distinct ID where ID Occurs more than once

Anonymous — Thu, 26 Apr 2018 15:16:46 GMT

No it is a simple count... i just hid part of the column name.

Re: Count Distinct ID where ID Occurs more than once

sunny_talwar — Thu, 26 Apr 2018 15:18:10 GMT

oh okay... can you post a screenshot with this new expression

Count({<ID = {"=Count(ID) > 1"}>}ID)

Re: Count Distinct ID where ID Occurs more than once

Anonymous — Thu, 26 Apr 2018 15:24:13 GMT

The second column is where your expression has been added

Re: Count Distinct ID where ID Occurs more than once

sunny_talwar — Thu, 26 Apr 2018 15:27:59 GMT

Oh so it is working... all you need to do is.... remove the first expression and under Add-ons -> Data handling uncheck 'Include Zero Values'

Re: Count Distinct ID where ID Occurs more than once

Anonymous — Thu, 26 Apr 2018 15:41:55 GMT

Sorry, could you send me an example with the first expression removed?

Re: Count Distinct ID where ID Occurs more than once

sunny_talwar — Thu, 26 Apr 2018 15:44:57 GMT

example? I am not sure I follow...

Re: Count Distinct ID where ID Occurs more than once

Anonymous — Thu, 26 Apr 2018 15:48:19 GMT

You said remove the first expression... what do you mean by removing the first expression?

Re: Count Distinct ID where ID Occurs more than once

sunny_talwar — Thu, 26 Apr 2018 15:49:19 GMT

You have two expressions in the chart? delete the one which is just Count(ID) and let the new one stay.... does it make sense?

Re: Count Distinct ID where ID Occurs more than once

MarcoWedel — Thu, 26 Apr 2018 20:19:30 GMT

What's your expected result?

Re: Count Distinct ID where ID Occurs more than once

Blackadder — Wed, 14 Nov 2018 11:08:42 GMT

Hi Stalwar1

I came across your response to the question above, I have more or less the same issue (I need to count IDs when the ID appears greater than once) within a year. My expression is:

Count({<YearMRN = {"=Count(YearMRN) > 1"}>}YearMRN)

At the moment YearMRN is a variable which is concatenating 2 fields in the QVD (Year and MRN)

and my chart dimension is:

Year([Arrival Date])

but the chart is simply doing a count of all records

If you had any ideas as to why this isn't working I would really appreciate any feedback you might have

cheers!

Qlik accepts the expression but it