Set analysis with multiple tables

[Lauri]

I need to calculate the % of patients with Blood Pressure under 140/90, by year. Seems straightforward, but I'm in need of advice on the best approach to keep only certain patients, based on a comparison of dates from two tables.

My data are in tables with these fields (among others):

• Conditions: PatientID, ConditionStartDate
• BP: PatientID, ObservationDate, Systolic, Diastolic
• Visits: PatientID, VisitDate

I need a bar chart with Year of Visit as the dimension, and % of patients whose last reading of each year was under 140/90. I can include only patients whose ConditionStartDate is earlier than July 1 of the chart Year.

Should I combine the data into a new table so that I can perform the date comparison with set analysis, like this:

EventRowID={"=ConditionStartDate<MakeDate(year(VisitDate), 7, 1)"}

Or should I use the p() function, in my chart measure, with year fields created in the load script, like:

VisitYear=p(BPYear)

Or something else altogether?

Many thanks for your advice.

[krishna_2644]

some sample data please?

[Lauri]

Hi Krishna, thanks for the quick reply.

BP:

PatientID	ObservationDate	Systolic	Diastolic
545	4/9/2017	122	76
545	10/10/2017	139	80
678	3/1/2016	134	79
678	2/4/2017	141	91
910	5/5/2015	119	69

Visits:

PatientID	VisitDate
545	1/9/2016
545	4/9/2017
678	3/1/2016
678	1/10/2017
910	5/5/2015

Conditions:

PatientID	ConditionStartDate
545	2/10/2016
678	9/10/2016
910	11/11/2010

The bar chart will show Visit Years 2015, 2016, 2017.

Patient 545 will appear in 2016 and 2017 (because his condition started before 7/1 of both years).

Patient 678 will appear in 2017 only (because her condition started after 7/1/2016 but before 7/1/2017).

Patient 910 will appear in 2015 only (because he had no visits in 2016 or 2017).

[krishna_2644 · Accepted solution]

Hope this helps/this could be your staring point.

[Lauri]

Thank you so much for your help. So generally, do you put all fields into one table, when you need do filtering that compares fields?

[krishna_2644]

As i was having 2 fields (you can map or do left join) and the data is straight forward i have merged the two tables.but if theres is granularity of data or if theres is case where you shouldnt merge then just associate those two tables with a common field and do the analysis.