Groups share of total in RFM analysis

[#1]

Hi all,

I'm new to QlikView and now I meet some problems when trying to do some RFM analysis. It's mainly about range of the total.

In my case, I create some records for test.

let vStart = num(makedate(2008,1,1));
let vEnd = num(today());
let vRFMGroups = 10;

Temp:
load
 'Store ' & pick(ceil(rand()*5), 'A','B','C','D','E') as OrderStore,
 floor(rand()*1000+1) as CustomerID,
 date($(vStart)+floor(rand()*($(vEnd)-$(vStart))), 'YYYY-MM-DD') as OrderDate,
 ceil(rand()*1000) as OrderAmt
autogenerate 30000;

load
 recno() as OrderID,
 *
resident Temp
order by OrderDate;

drop table Temp;

RawData:

Because I am not so familiar with the RFM theory yet, I'll split it into several parts.

For the Monetary part, I have finished the 1st chart [Performance of customer groups: group by same nb of customers, order by amt of each customer DESC]

995 customers (with the filter [OrderStore=Store A]) are put into 10 groups, and each group has almost the same nb of customers (995/10=100).

For the dimension [Group], I use aggr and rank functions. like =aggr(ceil(rank(Sum(OrderAmt), 4)/100), OrderStore, CustomerID)

Now we can compare the performances of each group.

Next step, I want to group the customers by OrderAmt, also order by OrderAmt. That means each group will share ABOUT (normally not exactly)10% of the total sales. So how can I put the dimension [Group]?

The following is like what i would like to see.

You can see that the F (Amt) column is around 10% of the total, but not exactly.

So can anyone help to supply some solutions or suggestions?

Thanks a lot!

[Anonymous]

Hi,

have you worked out some reliable soultion  for RFM?

Thanks

Piotr