Calculate growth rate from customer base

[#1]

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).

[preminqlik]

regarding this observe the following expression which is return in denominator :

above(sum( aggr( rangesum(above(total sum({1}[New Customers]),0,rowno(total))),[Q-Year]))

for understanding just come from inner expression as follows

above(sum( aggr( rangesum(above(total sum({1}[New Customers]),0,rowno(total))),[Q-Year]))

>here as you know above function gives result till previous records

>here i restricted current selection by keeping {1}

>coming to next inner expression as follows :

rangesum(above(total sum({1}[New Customers]),0,rowno(total))

>here rangesum gives cumulative sum from and it also calculate the cumulative sum in a given range

in above expression i have given the range from 0 to rowno(total)  it means that : row restricted to total (till above records)....

> Now coming to next view

aggr( rangesum(above(total sum({1}[New Customers]),0,rowno(total))),[Q-Year])

here you have took the dimension as [Q-Year],

aggr function creates an virtual table whose dimension is [Q-Year] and which expression is rangesum(above(total sum({1}[New Customers]),0,rowno(total)))

> now again calculating the whole sum which gives the result of denominator to take the percentage as per your question.

(if the solution is correct mark it has correct , so that others can refer easily... )

if any doubts let me know....

-Many Thanks

Premhas

[#3]

thanks a lot for the detailed explanation. I'll try and let you know!!

[#4]

hmm, i get only 0s now

[#5]

it's strange. It looks like QV doesn't recognize the [New Customers] as a field to sum anymore... it's in black instead of dark red / brown.

[preminqlik]

i didnt get you , please let me know in breif...?

[#7]

Hi,

I can't seem to add restrictions to the set as you suggested...

the results are usually null.

I tried playing around with the { } and ( ) but that didn't work (see below comment) and one occasion it returned 0s.

Very strange.

[preminqlik]

hi there,

k, send me sample qvw

lets c...

..

[#9]

can't... confidential information

[preminqlik]

hey hi there,

hope this example helps u

[#11]

thanks a lot! your help is very much appreciated!