Report on Top 20% of Customers 1

[byrne1]

I am trying to produce a report on the top 20% of our customers for the past 12 months, based on our SALES table (linked to our CUST table via CUSTNUM field). I want to get a count and total $ amount spent, but when I run the report it calculates these totals for the *entire* customer database (all sales for the past 12 months).

The way that I've created the report is to group the sales by customer number, show total amount spent for each customer, and then only show the top 20% based on this total. The printed report shows up just fine, but when I try to present a count of these customers, or the dollar amount spent, it calculates it for the entire 12 month sales history, not just the top 20%.

Any suggestions? What more information do I need to supply? I am running CR 10.0 Developer edition.

 

[synapsevampire]

Use the Top N feature in Crystal for this, I think that it's under Report as Group Sort Expert or Top N in CR 10.

Select top 20 percent and it should be what you're after.

-k

 

[byrne1]

I have done that but when I, say, want a count of CUSTNUM (to get the actual # of customers in my top 20%) that appears in the report, it shows a count of approx 41,000 (the total number of records in my customer table).

In a nutshell, here are the tables and fields I am using:

SALES
(tickettotal)
(custnum)*

CUSTOMER
(custnum)* %
(name)

*=linked fields
%=used in group

I have set the TopN setting for the group to show the top 20% based on sum of the tickettotal field. When I try to perform a count of CUSTNUM, it shows approx 41,000.

 

[lbass]

Insert a running total to do the count, instead of using an inserted summary. The running total will only count the displayed records.

-LB

 

[byrne1]

I cannot insert any running totals b/c all of my printed information is on the group header. Since each customer may have multiple records in the SALES table, I suppress the detail data and summarize the TICKETTOTAL field. This information is printed on the group header.

I could probably calculate a running total on the total sales amount for our top 20% for the past 12 months, but how can I get a customer count out of this? If one customer has five sales, then the RT would potentially count him five times.

 

[lbass]

Of course you can insert a running total. Go to the field explorer->running total and create it there. For the count, choose distinctcount of the customer field, evaluate for each record, reset never.

For the sales field, choose sum of tickettotal, evaluate for each record, reset never. Place both running totals in the report footer.

-LB

 

[byrne1]

Well, that sure did work just fine! I was trying to right-click the field and then select Insert > Running total and that was not working.

Thank you for your guidance.