topic Month by month customer retention in QlikView

Month by month customer retention

Tue, 22 Nov 2016 10:41:41 GMT

Hi,

I'm trying to make a table to show month by month customer retention. After reading lost of posts, I have achieved to create expressions that work for a selected year-month, using p() and e() and a continuous Month Count but can't get to make it work using year-month as dimension, even with an as of Month Count table that flags current and previous month.

The source data contains user, date and product for each of the customer transactions.

The goal is to get the following counts, and these are the expressions:

- Customers that bought a product the current OR the previous month.

=count(distinct{1<username=p({1<product={'apples'},MonthCount ={$(=max(MonthCount))}>}username)+p({1< product={'apples'},MonthCount ={$(=max(MonthCount)-1)}>}username)>}username)

- Customers that bought a product the current AND the previous month.

=count(distinct{1<username=p({1< product={'apples'},MonthCount ={$(=max(MonthCount))}>}username)*p({1< product={'apples'},MonthCount ={$(=max(MonthCount)-1)}>}username)>}username)

- Customers that bought a product the current AND NOT the previous month.

=count( distinct{1<username=
p({1<product={'apples'},MonthCount ={$(=max(MonthCount))}>}username)
*e({1<product={'apples'},MonthCount ={$(=max(MonthCount)-1)}>}username)
>}username)

- Customers that bought a product the previous AND NOT the current month.

=count(distinct{1<username=e({1< product={'apples'},MonthCount ={$(=max(MonthCount))}>}username)*p({1< product={'apples'},MonthCount ={$(=max(MonthCount)-1)}>}username)>}username)

In the asof table i have asofmonthcount , monthcount, and monthsago, where asofmonthcount is linked to each monthcount and the previous, which are flagged as monthsago=1 or monthsago=0.I have tried using asofmonthcount as dimension and changing MonthCount ={$(=max(MonthCount))} for monthsago={0}, and MonthCount ={$(=max(MonthCount)-1)} for monthsago={1}, but not getting the desired result.

Any help will be much appreciated, I have been a long time stucked with this!

Thans in advance,

Jorge

Re: Month by month customer retention

vinieme12 — Tue, 22 Nov 2016 11:28:52 GMT

Not sure if my post here can be helpful

How can I reference the values from Dimensions in set analysis?

Re: Month by month customer retention

Tue, 22 Nov 2016 13:17:15 GMT

Thanks for your answer Vineeth. It seems a similar problem, but as I'm using personal edition and can't open your posted qvw I don't know if your solution applies

Re: Month by month customer retention

vinieme12 — Tue, 22 Nov 2016 13:21:25 GMT

can you post a sample app?

Re: Month by month customer retention

Tue, 22 Nov 2016 14:21:49 GMT

Hi Vineeth, find attached a simplified app that represents the same problem.

Thank you!

Re: Month by month customer retention

Tue, 22 Nov 2016 15:43:57 GMT

I think the problem might the asof table (and the model), as I'm relating all facts to both current and previous months, therefore addition and intersection give the same results, and both substractions equal 0.

Still can't think of how to avoid this and get a solution...

Re: Month by month customer retention

vinieme12 — Wed, 23 Nov 2016 08:01:26 GMT

stalwar1 Your expertise are needed here

Re: Month by month customer retention

Wed, 23 Nov 2016 08:46:38 GMT

Thanks again Vineeth. I attach the latest version of the test app. I guess that with the asofmonth dimension and the correct expressions this cuold work, but still haven't figured out how.

Re: Month by month customer retention

sunny_talwar — Wed, 23 Nov 2016 10:34:19 GMT

vinieme12‌ I will def. take a look at this, but from what I read it seems that we would need to somehow use Aggr() function instead of set analysis to do this. I may be wrong, but I will try to play around with this to see if I can get it to work.

Re: Month by month customer retention

vinieme12 — Wed, 23 Nov 2016 10:36:33 GMT

Hi jorgeplanas ,

I am also trying to work out a solution

Re: Month by month customer retention

Wed, 23 Nov 2016 10:50:40 GMT

Thanks a lot stalwar1‌ and vinieme12‌!

I'm also working on it, tried something with aggr(min(month),username) and aggr(max(month),username) inside of the asofmonth dimension but still couldn't make it work.

Looking forward for your insight

Re: Month by month customer retention

sunny_talwar — Wed, 23 Nov 2016 12:44:11 GMT

first things first, do you have QV12 or not? If not then you will need to fix the sort order for your month field in the script. I did that by adding a simple inline load which was later dropped.

At start of the script

Temp:

LOAD RecNo() as month

AutoGenerate 12;

At end of the script

DROP Table Temp;

Once you have this done, the aggregate function should start working for you.

Dimension

month

Expressions

1) =RangeSum(Column(2), Column(3), Column(4))

2) =Sum(Aggr(If(username = Above(Only({1}username)) and month = Above(Only({1}month)) + 1, 1, 0) , username, month))

3) =RangeSum(Count(DISTINCT username),

-Sum(Aggr(If(username = Above(Only({1}username)) and month = Above(Only({1}month)) + 1, 1, 0) , username, month)))

4) =RangeSum(Above(Count({1}DISTINCT username)),

-Sum(Aggr(If(username = Above(Only({1}username)) and month = Above(Only({1}month)) + 1, 1, 0) , username, month)))

Re: Month by month customer retention

Wed, 23 Nov 2016 15:18:34 GMT

It's working perfectly! I would have never come to this approach, I'll have to explore a bit more the aggr() and above() functions.

I'll try now to implement it in the real app, I guess it will work fine.

Thank you very much stalwar1‌ and vinieme12‌‌‌!

Re: Month by month customer retention

sunny_talwar — Wed, 23 Nov 2016 15:20:54 GMT

If in case you have QV12, this will be a good place to read -> The sortable Aggr function is finally here!‌‌. Actually even if you don't have QV12, I would suggest you to read this

Re: Month by month customer retention

Wed, 23 Nov 2016 15:33:56 GMT

thanks for the info, I'm using QV11, but I'll check it anyway!