Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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):
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.
some sample data please?
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).
Hope this helps/this could be your staring point.
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?
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.