1 Reply Latest reply on Mar 22, 2018 2:28 PM by Deepak Rai

    Calculated field for time delta between grouped rows

    Taylor Floyd

      I'm attempting to calculate the difference in time between grouped rows(by METADATA1) of data that share identical information in a field. Pseudo-code for this would be [for distinct METADATA1, calculate time delta between previous row and current]. There is no preference whether it rounds down or up depending on the (seconds) evaluation.

       

      I've included sample data with desired calculated field in red below:

       

      METADATA1

      ENDDATETIME

      PROPERTYVALUES

      METADATA2

      CalcField

      ident: zz0000101-Jan-2018 10:54:38DOCUMENT/NOTESDocument Viewing0
      ident: zz0000101-Jan-2018 10:54:41NOTES/NOTESNotes0
      ident: zz0000101-Jan-2018 10:59:41DOCUMENT/NOTESDocument Viewing5
      ident: zz0000101-Jan-2018 10:59:44NOTES/NOTESNotes5
      ident: zz0000202-Jan-2018 10:43:09NOTES/NOTESNotes0
      ident: zz0000202-Jan-2018 10:44:37HISTHist 1
      ident: zz0000202-Jan-2018 10:44:40NOTES/NOTESNotes1

       

      My current formula is:

       

       

      1. IF LOOKUP(ATTR([Metadata1]),-1)!= ATTR([Metadata1]) 
      2. THEN 
      3. DATEDIFF('minute', LOOKUP(ATTR([Enddatetime]),-1),ATTR([Enddatetime])) 
      4. END 

       

      Any help would be greatly appreciated.