How to get the number of patients whose last lab result is more than 9?

[baylor2016]

Hi Experts,

I am trying to find a way to get the number of patients whose last lab result is more than 9. I used this function, it does not give me the number correctly. Please help.

=sum(
if(AGGR(FirstSortedValue(A1C, -LABORDERDATE2), PAT_ID)>9, 1,0)
)

PAT_ID	LABORDERDATE2	EncounterID	A1C	RANK
1	03/13/2017	1	7.8	1
2	09/01/2016	2	6.3	1
3	09/08/2016	3	7.0	1
4	05/30/2017	4	7.7	1
4	02/14/2017	5	7.5	2
4	10/17/2016	6	8.7	3
4	08/12/2016	7	8.6	4
5	07/11/2017	8	5.9	1
5	03/07/2017	9	6.3	2
5	08/30/2016	10	5.0	3
6	04/03/2017	11	7.5	1
7	06/06/2017	12	7.5	1
8	03/22/2017	13	12.0	1
9	06/14/2017	14	7.4	1
10	10/27/2016	15	10.6	1
11	05/17/2017	16	11.0	1
11	03/30/2017	17	13.8	2
11	09/20/2016	18	15.1	3
12	06/12/2017	19	5.0	1
12	12/07/2016	20	5.8	2
13	02/03/2017	21	6.6	1
14	03/02/2017	22	9.9	1
15	12/02/2016	23	5.5	1
16	06/29/2017	24	8.2	1
16	03/23/2017	25	7.4	2
17	07/19/2017	26	7.9	1
17	03/24/2017	27	9.0	2
18	04/14/2017	28	9.0	1
18	08/16/2016	29	8.3	2
19	07/18/2017	30	11.3	1
20	10/21/2016	31	5.5	1

sample data and project attached

Thanks in advance

Longmatch

[Anil_Babu_Samineni]

May be?

=If(Sum(A1C) > 9, Count(LABORDERDATE2))

[Anil_Babu_Samineni]

Or this?

Count({<A1C = {'>9'}>} LABORDERDATE2)

[effinty2112 · Accepted solution]

Hi Haijunw,

Maybe:

=count(Aggr(if(FirstSortedValue(A1C,-Date#(LABORDERDATE2,'MM/DD/YYYY')) >9,PAT_ID),PAT_ID))

Regards

Andrew

[mostwanted123]

Try this,

= Count({<A1C = {' > 9'}>}PAT_ID)

Regards,

Pratik Bhor

[baylor2016]

Thank you for the quick solution. It works perfectly!!!

[baylor2016]

Another question, if I need to get the  the number of patients whose last lab result is more than 9 in 2017, how to modify the script? Thanks

[effinty2112]

Hi,

Try:

=count({$<LABORDERDATE2 = {">=01/01/2017"}>}Aggr(if(FirstSortedValue(A1C,-Date#(LABORDERDATE2,'MM/DD/YYYY')) >9,PAT_ID),PAT_ID))

Regards

Andrew

[baylor2016]

Not working. I created another post. You can answer my questions there.  I actually wanted to ignore a filter, but it does not work. The same method works in other script, not this one. Strange. Thanks