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

# 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:

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

• ###### Re: Calculate growth rate from customer base

See attached example.

• ###### Re: Calculate growth rate from customer base

Many thanks!

I hope it will work on the much more complicated model I have already in place

Can you please explain this line:

rangesum([New Customers],peek('TotalCustomers')) as TotalCustomers

Thanks a lot!

• ###### Re: Calculate growth rate from customer base

It sums the value of [New Customers] of the current record of the source table with the value of TotalCustomers of the previous record of the target table. Peek is used to retrieve that value from the target table.

• ###### Re: Calculate growth rate from customer base

thank you.

It just seems weird since you would expect to see dates, at least for the start date, in the expression or at least I would...

I wonder how I could take this one to the next level. I want to add churns, which are customers that left each month.

• ###### Re: Calculate growth rate from customer base

Hi again,

Is there a way to accomplish the above just in the expression level, nothing in the load process?

• ###### Re: Re: Calculate growth rate from customer base

thanks for asking this question , today i did R&D and tried this for you in UI level(expression level)..(hope it helps me in future if any requirement)

hope this helps you....

-Many Thanks

Premhas

• ###### Re: Re: Calculate growth rate from customer base

Thanks a lot! Can you walk me through the expression please?

• ###### Re: Calculate growth rate from customer base

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

• ###### Re: Calculate growth rate from customer base

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

• ###### Re: Calculate growth rate from customer base

so we used the 1 to override any selections...

now my question is this:

how do I allow the selection of certain fields to still have an effect? For instance, I'd like the expression to be agnostic to the time period selected, but I do want it to be affected by CustomerType.

Can that be done?

Thanks!

• ###### Re: Re: Calculate growth rate from customer base

also, how would I add restrictions to the expression? (e.g. only sum if CustomerType={'Nice','Justin Bieber Fan'} ) ?

• ###### Re: Calculate growth rate from customer base

hi yup,

here the expression for new customers with your restrictions

sum({<CustomerType={'Nice','Justin Bieber Fan'} >}[New Customers])

and here is the restrictions for growth for your application :

sum({<CustomerType={'Nice','Justin Bieber Fan'} >}[New Customers])/above(sum( aggr( rangesum(above(total sum({1{<CustomerType={'Nice','Justin Bieber Fan'} >}}[New Customers]),0,rowno(total))),[Q-Year])))

once try and let me know.

-Many Thanks

Premhas

• ###### Re: Calculate growth rate from customer base

hmm, i get only 0s now

• ###### Re: Calculate growth rate from customer base

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

• ###### Re: Re: Calculate growth rate from customer base

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.

• ###### Re: Calculate growth rate from customer base

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.

• ###### Re: Calculate growth rate from customer base

hi there,

k, send me sample qvw

lets c...

..

• ###### Re: Calculate growth rate from customer base

can't... confidential information

• ###### Re: Re: Calculate growth rate from customer base

hey hi there,

hope this example helps u

• ###### Re: Calculate growth rate from customer base

thanks a lot! your help is very much appreciated!