Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
evgeniyita
Contributor

"Recency-Monetary" clients' distribution

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;