Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Hi,
have you worked out some reliable soultion for RFM?
Thanks
Piotr