Date Difference

3 followers
0
Avatar

The above gives a brief description of the table and how it should be interpreted.

 

The below given is the table I have,

The result should look like this,

Sunil Tetali

1 comment

  • Avatar
    Pablo Redondo

    Hi

    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"
    • CONCAT("Set ";GROUPROWNUMBER()) “Set”

    Hope this helps.

    0
Please sign in to leave a comment.