5 Replies Latest reply on May 10, 2013 8:17 AM by Steven Levey

    Average Customer Lifespan Calculation (Across ALL customers)

    Steven Levey



      I am trying to create a CALCULATED FIELD that will give me the AVERAGE number of months between the MIN(DATE) and MAX(DATE) for ALL the customers in the data source. The reason I need this in a calculated field is I am using this value in another calculation.


      (Avg. Customer Lifespan) x (Avg. Customer Spend) = Avg. Customer Lifetime Value


      The Challenge:


      I need to be able to do this by NOT putting the Customer pill on the View in order to get the correct value, as then I CANT use it in the calculation above.


      • I have tried WINDOW_AVG(DATEDIFF('month',MIN([Order Date]),MAX([Order Date]))), however the customer has to be on the view.
      • I have tried DATEDIFF('month',MIN([Order Date]),MAX([Order Date])) but when I remove the customer it gives me the MAX value of 47 months where the average across all the customers should be 32.95 months.
      • I have tried to Average the DATEDIFF calculation but cant Aggregate and Aggregate.
      • I have tried creating other calculated fields which remove all the customers OUT the view, so it shows the right figure BUT then cant be used in another calculation without some additional filtering and hacking away at hidden headers etc. (See attached)


      I am almost sure I can achieve this through clever use of Table Calculations or clever use of formula functions but just cant see to get it.


      I have attached the workbook, using Sample Store Data and my "misguided" attempts at a solution.


      Can anyone help?


      Thanks in advance