Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Groups share of total in RFM analysis

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:

error loading image

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)

error loading image

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.

error loading image

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!

1 Reply
prb
Employee
Employee

Hi,

have you worked out some reliable soultion  for RFM?

Thanks

Piotr