My company has asked me to put together a model which shows customer retention. They want to know how many customers they have retained by type of service over a 5 year period. Here is an example:
2007
Service Customers Retained from 2006 Customers Lost from 2006 Customers New from 2006
123
They want the same information for 2007, 2008, 2009 and 2010. Sort of stumped on this one.
Attached is one approach. The main data contains a Year, ServiceType and Customer. I create an AsOf table to connect an AsOfYear to the Year field by a YearType of 'Current' or 'Previous'. I create a chart with ServiceType and AsOfYear as dimensions. Then:
New = -sum(aggr(only(YearType)='Current' ,AsOfYear,ServiceType,Customer)) Lost = -sum(aggr(only(YearType)='Previous',AsOfYear,ServiceType,Customer)) Retained = -sum(aggr(count(distinct Year)=2 ,AsOfYear,ServiceType,Customer))
