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;
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?