topic About retention rate in QlikView

About retention rate

Mon, 27 Dec 2010 08:48:19 GMT

Hi all,

I was wondering how to calculate retention rate:

I load the raw data into QV:

OrderId MemberId Amount

1 1 $30

2 1 $35

3 2 $30

4 2 $30

5 3 $11

I want to use an object to show how many people buy 3 times, 2 times, 1 times and so on.

In this sample, the results should be:

2 times: 2

1 times: 1

How can I achieve it and I should use which object to show?

Thanks.

About retention rate

Miguel_Angel_Baeyens — Mon, 27 Dec 2010 10:20:07 GMT

Hello Isaac,

Take a look at the code posted here to see if that helps.

Regards.

About retention rate

Mon, 27 Dec 2010 10:28:12 GMT

Hello Isaac,

I would use a (bar-) chart with MemberId as Dimension. The expression is similar to:

rank(Count(OrderId))

Default sort-order is ascedning, You can order it descending if you like.

Regards, Roland

About retention rate

Mon, 27 Dec 2010 10:34:21 GMT

Hi Miguel,

I answered in parallel. Seems you are (sometimes) a little bit faster.

Nevertheless I tested your link and my browser failed with an object refernce error. Would you validate your address?

Kindly regards, Roland

About retention rate

Miguel_Angel_Baeyens — Mon, 27 Dec 2010 10:44:23 GMT

Hello Roland,

Thanks for noting. I've deleted the link and added it again and it seems it's working now. Your solution may be easier, as I'm suggesting to create that field in the script.

Thanks again and Regards.

About retention rate

Mon, 27 Dec 2010 14:12:55 GMT

Hi Roland,

Thanks for you answer, but I don't want to show specific MemberId as the dimension, the dimension should be "Times":

charged 1 times, 2 times,3 times, and the corresponding value is 1, 2, 0.

So can I create a custom dimension for Times?

Thanks.

About retention rate

Miguel_Angel_Baeyens — Mon, 27 Dec 2010 14:29:38 GMT

Hello,

Check something like the following in the script

OriginalData:LOAD * INLINE [OrderId, MemberId, Amount1, 1, $302, 1, $353, 2, $304, 2, $305, 3, $11]; RankedData:LOAD OrderId, MemberId, Amount, IF(MemberId = PREVIOUS(MemberId), RANGESUM(PEEK('Times'), 1), 1) AS TimesRESIDENT OriginalDataORDER BY MemberId; DROP TABLE OriginalData;

Now you have the "Times" field you can use as dimension. Playing a bit with the code will display more accurate results.

Hope that helps

About retention rate

Mon, 27 Dec 2010 14:53:13 GMT

Hi,

If you want to do this in chart, here is the solution (see the file attached):

1. Load data

2. Add Calculated dimension :

Aggr(Count(OrderId), MemberId)

3. Add Expression:

=Count(distinct MemberId)

But be careful - if there is a lot of data - use script version!

Kind regards,

Miha

About retention rate

Mon, 27 Dec 2010 15:16:31 GMT

Oh dear Miha,

Many thanks for your expression, it works well for me.

And also thanks the script of Miguel Angel Baeyens, it also helps me solve another question.

Thanks for your every experts!

Isaac Li