Hi everyone,
I am trying to create a chart to monitor buyer retention. I want to track how many buyers have purchased also n the previous month and how many are new buyers (or reactivated). A limitation in doing so is given by the fact that I cannot use the load editor and create a flag in the backend of the app.
The goal is a chart like this (mock up):
![Defo_0-1701162374367.png Defo_0-1701162374367.png](https://community.qlik.com/t5/image/serverpage/image-id/120667i9D5D881EED6B1CAB/image-size/medium?v=v2&px=400)
At the moment I am achieving this evaluation only in a table, with a combination of Above and Aggr functions:
![Defo_1-1701162467366.png Defo_1-1701162467366.png](https://community.qlik.com/t5/image/serverpage/image-id/120668iC202E5478973278B/image-size/medium?v=v2&px=400)
But when I try to convert this logic in the bar chart it doesn't work anymore (probably because I am loosing the USERID dimension used in the Aggr() ).
Here the formulas used:
- Count({<Purchase_amount={">0"}>}DISTINCT USERID) -> Buyer
- if(
if(USERID = Above(USERID),1)
+ if(above(aggr(count(distinct {<Purchase_amount={">0"}>} USERID),USERID,Month))=1,1)
+ if(Month = Above(Month)+ 1,1)
<>3,count(distinct {<Purchase_amount={">0"}>} USERID),0) -> Buyer M0
- if(
if(USERID = Above(USERID),1)
+ if(above(aggr(count(distinct {<Purchase_amount={">0"}>} USERID),USERID,Month))=1,1)
+ if(Month = Above(Month)+ 1,1)
=3,count(distinct {<Purchase_amount={">0"}>} USERID),0) -> buyer M-1
I have tried to summarize the problem in the clearest way possible, but I think it's a bit tricky. So, I am open to sharing more details. Any other ideas or approaches to this problem are welcomed.