3 Replies Latest reply on Jun 24, 2013 1:21 PM by Shawn Wallwork

# calculate average datediff of first two orders only

Trying to determine the average number of days it takes from a first purchase to the second purchase.  I have a calulation that gives me the datediff between each date, but can't figure out how to select only the first date and the number of days to the second.  If there is only one purchase date then the value is null and can be ignored, if there are more than two orders I want to ignore the rest of those orders in the calculation.  Attached a doc of what I have so far.

My calculated field is:  DATEDIFF('day',([MIN BILL_DT]),LOOKUP(([MIN BILL_DT]), 1))

from the calculation definition:

Results are computed along Day of BILL_DT for each DLR_NAME, PROD_SEG_B.  Relative LOOKUP values are fetched at the Day of BILL_DT level.

Message was edited by: Vincent Atwood I have attached a twbx, which shows order history for 5 customers with the date of each purchase, and the datediff from each order to the next.  What I am trying to do is just grab the FIRST order date entry for each customer.  For example, the first customer, 'MEGA....' has a first order date of Aug 4, 2007 with a datediff of '3' until the second order - placed on Aug. 7, 2007 - I only want that line of data, not all the other dates and datediff numbers. Two of the samples only have one order so i would grab just that one order with no value for datediff. Hope someone can help.  Thanks.

• ###### 1. Re: calculate average datediff of first two orders only

Vincent, this is a complex enough request that it would be best to post a sample packaged workbook (twbx) with just a bit of data (or use the Superstore dataset). Also move this post over to the Forums proper. Out here on the front page of the Community not many eyes will see it.

--Shawn

• ###### 2. Re: calculate average datediff of first two orders only

Hi Shawn – how do I move the post to Forums?  I thought that’s where I was posting it ;o  after your update from my previous post - aagghh

• ###### 3. Re: calculate average datediff of first two orders only

Here you go:

And to get to the forums so you'll always post there click forums: