2 Replies Latest reply on Nov 14, 2018 6:58 PM by Dana Chaffin

    Blending With Multiple Date Comparisons

    Dana Chaffin

      I have two datasets from two different data sources


      Data 1: Visits and Cases (Joined Table) (with visit ID, case IDs, employee ID, body part treated, and visit dates) on when someone goes to the athletic trainer for PT

      Data 2: Injury incident report (with employee ID, incident report date, body part injury) on someone who reports an injury


      Note: Data is provided in Excel


      With these two datasets I have three different scenarios (I'm only interested in the bold one)


      • A person can go to PT without reporting an injury
      • A person can report an injury and doesn't have to go to PT
      • A person can report an injury and goes to PT


      The dashboard I'm creating wants to track to see if a person reports an injury within 30, 60, and 90 days of the last visit to the athletic trainer for the same body part (i.e. the person can report an injury for a different body part within the last 180 days and it won't count)

      The person usually has multiple visits (for the same body part) and could have multiple cases open (for different body parts)


      So far so good...


      Here's what I've done so far:

      • Blended the two datasets on body part and employee ID - using Data 1 as primary
      • Fixed the MAX Visit Date to Visit ID and body part
      • Created calculated fields of MAX plus 30, 60, and 90 days


      Where I need help:

      A person can report an injury multiple times for the same body part (giving me a * when I pull in the incident report date) and I need all of the injury report dates to compare with the case date

      • I still struggle with data blending - I understand the concepts and have been successful in the past (although not without taking me a while to get right)

      The data needs to be able to break out by different date parts (year, quarter, month, etc) so I don't think having a FIXED MAX on the injury table will work (I tried without success - I could be wrong)


      How can I get a unique count of reported incidents that visited PT in the last 30, 60, and 90 days?


      I've attached a mock-up spreadsheet (two tabs) which represents the data (numbers and injuries have been altered)


      Thanks for the help