5 Replies Latest reply: Apr 23, 2018 5:31 PM by Krishna Nagulapally RSS

    Set analysis with multiple tables

    Lauri Scharf

      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:


      Or something else altogether?


      Many thanks for your advice.