Hello,
Description:
I'd like to realize a part of RFM analysis (Recency-Monetary matrix, as an example). I've made a sample in QlikView (script is below) but I'm not sure that my way of implementation is correct.
Question:
1. Is my way of calculation make sense? If it's not - how to do it in a better way?
2. As you can see, customers are segmented in a load script based on the full period's data. So we can use the "MR" as a dimension in charts and build a visualization (sales per group, count of client per group, etc.) which will work correct based on selection. But how to look at the clients' distribution (based on a scatter chart; screenshot is attached)? If we would select something results were wrong.
Script:
Groupping:
load
ClientID,
if( SalesType = 1 and DateType = 1 , 'One-off' ,
if( (SalesType = 2 or SalesType = 3) and DateType = 1 , 'Lost',
if( SalesType = 1 and (DateType = 2 or DateType = 3), 'Newbie',
if( SalesType = 3 and DateType = 3 , 'VIP' , 'Growth' )))) as MR
;
load
ClientID,
if( sum(Sales) <=300, 1 ,
if( sum(Sales) > 300 and sum(Sales) <= 800 , 2 , 3 )) as SalesType,
if( max(Date) <= $(vMaxDate)- 3, 1 ,
if( max(Date) > $(vMaxDate) - 3 and max(Date) <= $(vMaxDate)-1 , 2 , 3 )) as DateType
Resident Data Group by ClientID;
.png){kind=link}