Can RFM be done in Crystal

[sgk17]

My boss wants me to do some RFM (recency, frequency, monetary) analysis using crystal. Basically, each customer gets assigned a 3 digit RFM code. Each digit can be between 1 and 5. To figure out the "R", I would sort the customers according to the last time they ordered and divide them evenly into 5 parts. If there were 100,000 customers then the top 20,000 would get an "R" of 5, the next 20,000 would get an "R" of 4 and so on. The process then gets repeated for how frequently they order and how much money they've spent. Here's my problem. I can do three separate reports that figures out the "R", "F", and "M" for each customer, but I don't know how to combine all of them together into one three-digit code for each customer. Is this possible? Any help or ideas would be appreciated. Thanks.

 

[IdoMillet]

You could do them as 3 subreports. Then, pass the result to the main report using "shared" variables.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[sgk17]

So I would have a main report and 3 subreports. The subreports would have to run first so do I create 3 detail sections to put each one into? I assume you mean that each subreport will have a shared variable and the main report will have a formula to concatenate all three together, right? Each customer for each subreport will be assigned a certain digit. Will this all map to the correct customer number in the main report? How?

 

[IdoMillet]

You can place all 3 subreports in the Group Header for each customer. The shared variables will assume their correct values in the section below.

The alternative is to place the 3 subreports in the Report Header. Load 3 shared variable arrays with the correct info for all the customers, and search for the matching entry in the formulas for each cutomer.

hth,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[sgk17]

Thanks for the help! I have another question. How is the best way to assign these digits to each customer on each subreport. For example, if I have 100,000 customers I want the top 20,000 to get a 5, the next will get a 4 etc. Do I have to know the total number of customers beforehand and hardcode that information in? I'm trying to set a counter and then saying that if 1 <= counter <= 20,000 then 5 else if 20,000 < counter <= 40,000 ... I'm hardcoding the numbers. Is there a better way?