15 Replies Latest reply: Feb 15, 2018 3:04 PM by Lauri Scharf RSS

    Date comparison in set analysis

    Lauri Scharf

      I am having trouble writing set analysis to find events in a table based on related events in another table.


      I have two tables that are linked on a person identifier (Recipient_Unique_ID):


      Table 1 contains MedClaimDate, Recipient_Unique_ID, MedRowID* (among other fields)

      Table 2 contains PharmClaimDate and Recipient_Unique_ID, PharmRowID* (among other fields)

      *both made with RowNo() during load


      I have a straight table that should show data from Table 1 if PharmClaimDate is equal to MedClaimDate or MedClaimDate+5 days.

      I have tried these variations just to see if I'm on the right track:

      This returns all rows, ignoring the set analysis:

      Sum({<[MedRowID]={"$(=PharmClaimDate & '>=' & MedClaimDate)"}>} [MedicalClaims.Paid_Amount])


      This returns 0 rows:

      Sum({<[MedRowID]={"=(PharmClaimDate>=MedClaimDate)"}>} [MedicalClaims.Paid_Amount])


      This returns 0 rows:

      Sum({<Recipient_Unique_ID={"=(PharmClaimDate>=MedClaimDate)"}>} [MedicalClaims.Paid_Amount])

      Thanks for any help.