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

# Average Customer Lifespan Calculation (Across ALL customers)

Hi,

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?

• ###### 1. Re: Average Customer Lifespan Calculation (Across ALL customers)

I am almost sure I can achieve this through clever use of Table Calculations

If that's the case then you're looking for help from either Joe Mako

--Shawn

PS: Or Richard Leeke if he weren't about to embark on a whirlwind tour of our hemisphere

• ###### 2. Re: Average Customer Lifespan Calculation (Across ALL customers)

Thanks Shawn,

Hopefully one of the Jedi Knights will see this post and provide some insight.

Maybe I dont need Table Calcs at all but trying to avoid having to do the sums in SQL.

• ###### 3. Re: Average Customer Lifespan Calculation (Across ALL customers)

Solution:

After reading another POST to do with Average Weekday counts ( http://community.tableau.com/message/184951#184951)

I saw a Table Calc in there by Jonathan Drummey and thought it might help me....looks like it did.

Averaging Calculation: (Used it for all the values needed to create the final Lifetime Value - Just changing the WINDOW_AVG line to suit)

IF FIRST()==0 THEN

WINDOW_AVG(DATEDIFF('month',MIN([Order Date]),MAX([Order Date])), 0, (IIF(FIRST()==0,LAST(),0)))

END

I have attached the Workbook so anyone can have a look at how to calculate Customer Lifetime Value across all customers. I left the initial failed attempts in the workbook so you can see my progress towards the solution.

I am sure there are better ways to achieve the results, but this will do for now.

1 of 1 people found this helpful
• ###### 4. Re: Average Customer Lifespan Calculation (Across ALL customers)

@Steven - That looks like what I would have done, nice work! Also as an FYI, the ,0, (IIF(FIRST()==0,LAST,0)) optimization is no longer needed in Tableau v8.

@Shawn - For table calcs, Joshua Milligan, Jim Wahl, and Brad Llewellyn have all been demonstrating some good stuff lately.

• ###### 5. Re: Average Customer Lifespan Calculation (Across ALL customers)

Thanks Jonathan. Appreciate it and the tip for v8.