1 Reply Latest reply on Dec 28, 2012 5:24 PM by Joshua Milligan

    How to compute difference between records in a pane

    Dan Gerena

      I have data that looks like this:

      Employee #          Transaction Date  

      12345                    1/1/2012

      12345                    1/15/2012

      12345                    1/17/2012

      33221                    1/15/2012

      33221                    1/20/2012

       

      I want to compute the difference from the prior value for each respective employee, so the end result would be:

      Employee #          Transaction Date   Difference

      12345                    1/1/2012                 -  

      12345                    1/15/2012               15

      12345                    1/17/2012                2

      33221                    1/15/2012               -

      33221                    1/20/2012               5

       

      How the heck does one do this? Thanks!!

        • 1. Re: How to compute difference between records in a pane
          Joshua Milligan

          Dan,

           

          I was able to solve this by using the DATEDIFF with a LOOKUP table calculation.  With LOOKUP, you can get a value at an offset from the current position (so I used -1 for the previous compared to 0 for the current).

           

          The formula looks like this:

           

          DATEDIFF('day', LOOKUP(MIN([Transaction Date]), -1),  LOOKUP(MIN([Transaction Date]), 0))

           

           

          I used MIN([Transaction Date]) as table calculations require aggregates.  I knew in this data that MIN would work (MAX or AVG or ATTR would also work).

           

          I've attached a workbook.  Take a look and let me know if you have any questions.