topic Re: Count distinct in QlikView

Count distinct

david777 — Thu, 08 Jan 2015 13:35:49 GMT

Can anyone tell me how to count distinct pnr if hba1c is greater than 70 only for the last row for each pnr in the table below?

So person 62811 should't count because the last value is 60.

datanibbler — Thu, 08 Jan 2015 13:46:02 GMT

Hi David,

there are several ways, but Set_analysis would probably be the most elegant.

Just put sth like

COUNT({$<value = {$(= '>' & '70')}>} DISTINCT pnr)

HTH

Best regards,

DataNibbler

sunny_talwar — Thu, 08 Jan 2015 13:52:25 GMT

Hey David,

Please check the attached qvw and see if this is what you want.

Best,

agomes1971 — Thu, 08 Jan 2015 13:52:56 GMT

Hi,

that's a nit approach!!!

Regards

André Gomes

rubenmarin — Thu, 08 Jan 2015 14:01:29 GMT

Hi David, you can use:

=Sum(Aggr(If(FirstSortedValue(Value, -Date)>70, 1, 0), Person))

It will fail if the same Person have different values for the same date.

sunny_talwar — Thu, 08 Jan 2015 14:44:13 GMT

That's a very sleek way of doing it. I am glad I saw your method. Thanks for sharing it.

Best,