set analysis question!

[baylor2016]

Dear expert,

I need to show the last lab result from all the departments for patients in department 1, but I only want the patients from dept 1 appear in the list. The problem is that when no filter in the last test results column, the dept 2 patients show up in the straight table. Not sure how to solve this problem. For example, patient 1's last lest (7.7) is done in dept 2, it is what I need, but I do not want patients 8,9 10 in the list since they are from department 2

Please help. Sample project attached

Thanks in advance

stalwar1‌

Longmatch

EncounterID	PatientID	DeptID	FCMYN	A1C	VisitDate
1	1	1	1	12.6	1/1/2017
2	1	1	1	13	1/2/2017
3	1	1	1	11.9	1/3/2017
4	1	2	1	7.7	1/4/2017
5	2	1	1	12.8	1/5/2017
6	2	1	1	13	1/6/2017
7	2	1	1	9.1	1/7/2017
8	3	1	1	10.3	1/8/2017
9	3	1	1	6.2	1/9/2017
10	3	2	1	11.6	1/10/2017
11	4	1	1	14	1/11/2017
12	4	1	1	9.4	1/12/2017
13	4	1	1	13.7	1/13/2017
14	5	1	1	7.7	1/14/2017
15	5	1	1	14.9	1/15/2017
16	6	1	1	7	1/16/2017
17	6	1	1	7.1	1/17/2017
18	7	1	1	5.9	1/18/2017
19	7	1	1	10.9	1/19/2017
20	7	1	1	5.4	1/20/2017
21	8	2	2	8.9	1/21/2017
22	8	2	2	12.5	1/22/2017
23	8	2	2	8.5	1/23/2017
24	9	1	2	11.6	1/24/2017
25	9	1	2	6.9	1/25/2017
26	9	1	2	5.6	1/26/2017
27	10	2	2	8.1	1/27/2017
28	10	2	2	10.1	1/28/2017

PatientID	FCMYN	Visits	LastVisit	LastA1C
		20	1/20/2017	10.1
1	1	4	1/4/2017	7.7
2	1	3	1/7/2017	9.1
3	1	3	1/10/2017	11.6
4	1	3	1/13/2017	13.7
5	1	2	1/15/2017	14.9
6	1	2	1/17/2017	7.1
7	1	3	1/20/2017	5.4
8	2	0	-	8.5
9	2	0	-	5.6
10	2	0	-	10.1

[NZFei]

Check attached file please.

[baylor2016]

Hi Fei,

Thank you very much for your helps.

I am sorry I did not state my questions clearly and the sample is not appropriate. I just modified the sample. My questions is I want to show ALL A1C values for all patients whose FCMYN=1. For example, patient 1's last test result on 1/4/2017  is 7.7 done in dept 2. I cannot use filter FCMYN =1 since it will filter out the test in FCMYN=2. I also do not want patient 8,9 10 in the list since they are from department 2.

[ahaahaaha · Accepted solution]

Hi,

If I understand you correctly, then we need to cut off patients with observations only in department 2. Then the sum of possible unique values of FCMYN should not be 2 (1 - only department1, 2 - only department 2. 3 - patient was in two departments).

An example in the attached file.

Regards,

Andrey

[baylor2016]

What I want to get is the latest test results for patients whose FCMYN=1, no matter where the test is done. However your answer is correct. I do not understand this syntax-Sum (DISTINCT TOTAL <PatientID> FCMYN) <>2. Can you explain? Thanks

[NZFei]

Hi,

I think the problem is that when a patient is showing in both Dep1 and 2, how do you know if he BELONGS TO Dep1 or 2?  It could be possible that a patient from Dep2 also has some result in Dep1, right? Then how can we know that this patient is belongs to Dep2 and not 1?

I know this is just some sample data. In your real full data, you may have Dep3 or more. We can hard code to make it work for now for just two departments but for your real full data, it is better to clearly define the rules. Maybe somehow load another field to define the patient department (not the department that the tests are done but the department that the patients belong to)?

I think your first sample app makes sense to me. There are both TestDepID and the FCMYN in that sample. And I think my first reply is the right answer (so for patient1 the last visit is 1/04/2017 and the result is 7.7). What is the problem for that?

" I cannot use filter FCMYN =1 since it will filter out the test in FCMYN=2". This is not right, using FCMYN=1 will NOT filter out the test in FCMYN2 when you have TestDepID because the "test in FCMYN=2" is in TestDepID=2, not FCMYN=2.

Hope this can help.

Cheers.

Fei