Forum Discussion

amacmath's avatar
amacmath
Frequent Visitor
3 years ago

Check if a certain value exists on the same day

I have a set of data (sample below) that tracks entry of progress notes by employees into individual youth records. The 'MemberID' is a unique identifier for each youth. If an employee meets with a c...
Need Help
amacmath's avatar
amacmath
Frequent Visitor
3 years ago

This somewhat worked... - unfortunately the second part of this (which I didnt realize at first) is that there are a few different types of Notes that would need to be looked for...So, you will see in the table below I need to be looking to see if there is a 'TELEHLTH_AV' Note on the same day as a 'F/F2', 'CFT/2', or 'CFTAnRev' note type. Is there a way using your solution above to add additional note types?

 

In the example below - Employee 1 had a visit that took place via telehealth,  Employee 2 had an in-person visit, employee 3 had an in-person visit, and employee 4 had a telehealth. I need to be able to show a visual with a list of kids who ONLY had a telehealth visit.  

 

CaseMgrNameMemberIDNoteDateNoteType
Employee 11234510/17/2022 11:00CC2
Employee 115742110/26/2022 15:15CC2
Employee 115742110/28/2022 10:00CMO_OutRch
Employee 115742110/31/2022 14:20CC2
Employee 115742110/31/2022 14:30F/F2
Employee 115742110/31/2022 14:30TELEHLTH_AV
Employee 25678910/17/2022 11:00CC2
Employee 25678910/26/2022 15:15CC2
Employee 25678911/15/2022 10:00CMO_OutRch
Employee 25678911/28/2022 14:20CC2
Employee 25678911/30/2022 14:30F/F2
Employee 389101111/28/2022 13:00CFT/2
Employee 411121311/27/2022 12:00CFTAnRev
Employee 411121311/27/2022 12:00TELEHLTH_AV
Thomas_Daubert's avatar
Thomas_Daubert
Icon for Helper I rankHelper I
3 years ago

Hello,

 

If the condition, TELEHEALTH is true when there is 2 raws with certain values, you can simply add all values concerned in the filter : 

FILTER(Sheet1,Sheet1[NoteType] in {"F/F2", "TELEHLTH_AV"} ),

 

Then, by using a filter on the last calculated column you will obtain the list you are looking for.

 

Let me know if it works 😉

  • amacmath's avatar
    amacmath
    Frequent Visitor
    3 years ago

    So would I do, 

    FILTER(Sheet1,Sheet1[NoteType] in {"F/F2", "CFT/2", "CFTAnRev", "TELEHLTH_AV"} ),

     or 

    FILTER(Sheet1,Sheet1[NoteType] in {"F/F2", "TELEHLTH_AV"},FILTER(Sheet1,Sheet1[NoteType] in {"CFT/2", "TELEHLTH_AV"},FILTER(Sheet1,Sheet1[NoteType] in {"CFTAnRev", "TELEHLTH_AV"} )

    I need it to return "TELEHEALTH" if there are any of those three note types (F/F2, CFT/2, CFTAnRev) PLUS a TELEHLTH_AV note on the same day. 

    • Thomas_Daubert's avatar
      Thomas_Daubert
      Icon for Helper I rankHelper I
      3 years ago

      First one, 

      FILTER(Sheet1,Sheet1[NoteType] in {"F/F2", "CFT/2", "CFTAnRev", "TELEHLTH_AV"} ),