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

# How to compute difference between records in a pane

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

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.