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)))
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
thanks for the info, I'm using QV11, but I'll check it anyway!