Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Solution

Accepted Solutions
preminqlik
Specialist II
Specialist II

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)

Please find attachment

hope this helps you....

-Many Thanks

Premhas

View solution in original post

20 Replies
Gysbert_Wassenaar

See attached example.


talk is cheap, supply exceeds demand
Not applicable
Author

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!

Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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.

Not applicable
Author

Hi again,

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

preminqlik
Specialist II
Specialist II

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)

Please find attachment

hope this helps you....

-Many Thanks

Premhas

Not applicable
Author

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

Not applicable
Author

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

preminqlik
Specialist II
Specialist II

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