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

Month by month customer retention

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

1 Solution

Accepted Solutions
sunny_talwar

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

Capture.PNG

View solution in original post

14 Replies
vinieme12
Champion III
Champion III

Not sure if my post here can be helpful

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Not applicable
Author

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

vinieme12
Champion III
Champion III

can you post a sample app?

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Not applicable
Author

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

Thank you!

Not applicable
Author

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

vinieme12
Champion III
Champion III

stalwar1 Your expertise are needed here

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Not applicable
Author

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.

sunny_talwar

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.

vinieme12
Champion III
Champion III

Hi jorgeplanas ,

I am also trying to work out a solution

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.