20 Replies Latest reply: Oct 27, 2013 9:51 AM by Al On RSS

    Calculate growth rate from customer base

      Hi all,

      Here's a tricky one:

       

      Let's take a subscription business that signs up customers everyday (like a cable company, mobile phone provider, newspaper...). Let's assume for simplicity that once someone becomes a customer he/she never leaves (churns).

       

      What I'm trying to build is a table (and a chart based on it) which shows the growth % of the customer base.

      This should be calculated as:

      total customers added in a given period / total customers the company had at the very beginning of that period.

      Another way to look at the denominator is the total customers at the end of the previous period.

       

      So, if the company started operating in 2010, and signed up 10 customers every month, by the end of October 2010 it would have 100 customers. It will acquire 10 new customers in November as well, so the growth rate for the month of novemner 2010 would be 10/100 = 10%.

       

      It's important to stress that the denominator always has the number of customers from the beginning of time (or the time when the company started operation... ) up to the very beginning of the period in question or very end of the period before.

       

      Same goes for quarters. Sometimes you would want to calculate the growth per quarter, so you'll have to do:

      total number of new customers in a given Q / total number of customers at the start of the Q.

       

      So....

      how do you go about that?

      I'm completely dumbfounded!

       

      Would appreciate any help.

       

       

      I created some simple dummy data here:

      https://docs.google.com/file/d/0B_7-00fdslR7NkVzZk5uTE16OWc/edit?usp=sharing

      (note that the type of Q-Year is probably string)

       

       

       

      (In a way this is a follow up to this question: http://community.qlik.com/message/406456?et=watches.email.thread#406456 which yielded strange results at times. Anywhere, this question is more elaborate and concrete).