Count distinct

[david777]

Hi

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.

person	date	value
89376	2012-11-29	72
22860	2012-03-07	82
03317	2012-12-12	82
73557	2012-03-23	85
62811	2012-05-02	73
62811	2012-08-31	60
13325	2012-02-22	107
09081	2012-03-21	72
23305	2012-04-10	75
23336	2012-05-14	56
23336	2012-05-15	72
23336	2012-06-15	40
23336	2012-08-14	33
13541	2012-05-14	74
73413	2012-08-28	87

[datanibbler]

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]

Hey David,

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

Best,

S

[agomes1971]

Hi,

that's a nit approach!!!

Regards

André Gomes

[rubenmarin · Accepted solution]

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]

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

Best,

S