3 Replies Latest reply on Nov 11, 2016 1:32 PM by Shinichiro Murakami

    average of date diff

    Amanda Stewart

      HI, I'm looking to find the date diff between customers orders and then the average between all customers.

       

      I've added an example I think explains things better

      For example I have 3 customers with difference order dates, I need to find the date diff between each order by customer and then the average against all customers.

      I started creating a date diff:

       

      datediff('day',LOOKUP(attr([Date]),-1),attr([Date]))

       

      Then buckets from the date diff calculation, the problem is the final step when you group the date diff by the buckets, is it not possible to have the  average of the date diff (as in excel)?

       

      AVG( datediff('day',LOOKUP(attr([Date]),-1),attr([Date])))

       

      Any help is appreciated!!

       

      Thanks

      Amanda

      Ex>:

            

      Customerorder datedate diff by customerdate diff buckets
      A01/01/2016--This is the results I'm looking for!
      A02/10/2016275+100Row LabelsAverage of date diff by customer
      A03/11/20163230-401-101
      A04/12/20163130-4030-4031.2
      B05/02/2016--60-7062
      B06/03/20163030-4090-10093
      B08/07/20163130-40+100275
      B09/08/20163230-40Grand Total65.22222222
      B10/10/20166260-70
      B07/06/20169390-100
      C11/10/2016--
      C12/10/201611-10