topic Re: Calculate growth rate from customer base in QlikView

Calculate growth rate from customer base

Wed, 16 Oct 2013 12:30:29 GMT

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

Re: Calculate growth rate from customer base

Gysbert_Wassenaar — Wed, 16 Oct 2013 15:41:15 GMT

See attached example.

Re: Calculate growth rate from customer base

Thu, 17 Oct 2013 13:37:15 GMT

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

Gysbert_Wassenaar — Thu, 17 Oct 2013 13:45:46 GMT

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

Thu, 17 Oct 2013 14:26:05 GMT

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

Sun, 20 Oct 2013 06:34:10 GMT

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

preminqlik — Sun, 20 Oct 2013 20:38:57 GMT

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

Re: Re: Calculate growth rate from customer base

Tue, 22 Oct 2013 08:02:28 GMT

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

Re: Re: Calculate growth rate from customer base

Tue, 22 Oct 2013 08:42:11 GMT

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

preminqlik — Tue, 22 Oct 2013 09:48:25 GMT

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

preminqlik — Tue, 22 Oct 2013 10:12:05 GMT

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

Wed, 23 Oct 2013 09:07:31 GMT

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

Re: Calculate growth rate from customer base

Wed, 23 Oct 2013 09:23:05 GMT

hmm, i get only 0s now

Re: Calculate growth rate from customer base

Wed, 23 Oct 2013 09:39:17 GMT

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

preminqlik — Wed, 23 Oct 2013 10:00:41 GMT

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

Re: Re: Calculate growth rate from customer base

Wed, 23 Oct 2013 12:12:38 GMT

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

preminqlik — Wed, 23 Oct 2013 16:07:40 GMT

hi there,

k, send me sample qvw

lets c...

Re: Calculate growth rate from customer base

Thu, 24 Oct 2013 11:34:30 GMT

can't... confidential information

Re: Re: Calculate growth rate from customer base

preminqlik — Sat, 26 Oct 2013 05:06:34 GMT

hey hi there,

hope this example helps u

Re: Calculate growth rate from customer base

Sun, 27 Oct 2013 13:47:43 GMT

thanks a lot! your help is very much appreciated!