Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)))
Not sure if my post here can be helpful
How can I reference the values from Dimensions in set analysis?
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
can you post a sample app?
Hi Vineeth, find attached a simplified app that represents the same problem.
Thank you!
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...
stalwar1 Your expertise are needed here
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.
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.
Hi jorgeplanas ,
I am also trying to work out a solution