Date Difference


1 comment

  • Pablo Redondo


    I believe the best approach to calculate your sets is "sessionizing" your data using the GROUPBYGAP() function. The groupbygap takes a timestamp and the maximum time between each event that would be considered part of the same grouping/set, thus 14 days is our value.

    Here are the details:

    1. In a new sheet run this functions

    • GROUPBY(#PatientServiceData!Pationet_ID) “Patient_ID"
    • GROUPBYGAP(#PatientServiceData!Service_Date;14d) “Start_Date"
    • GROUPMAX(#PatientServiceData!Service_Date) “End_Date”
    • GROUPCOUNT() “Day Count"

    2. Then I assume you want to filter sets that have only one day, thus you can filter where day count is > 1.

    3. Finally if you need to count your sets create another sheets with this functions:

    • GROUPBY(#Sheet1!Pationet_ID) “Patiend_ID"
    • COPY(#Sheet1!Start_Date) “Start_Date"
    • COPY(#Sheet1!End_Date) “End_Date"

    Hope this helps.

    Comment actions Permalink

Please sign in to leave a comment.