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

    calculate average datediff of first two orders only

    Vincent Atwood

      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.