Date Difference

Follow

Sunil Tetali

• March 03, 2016 22:39

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,

0

• 

  Pablo Redondo

  • March 04, 2016 15:26
  • Edited

  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

  Comment actions Permalink

Please sign in to leave a comment.