Sum of distinct count

[cathyg18]

I am grouping providers by day, using DistinctCount of customer account number to arrive at a number of customers seen per day per provider. I chose DistinctCount because one customer may have several transactions within a day.

I want to total the daily count by the week, to arrive at a total number of customers seen for the week for the provider. DistinctCount no longer works, because a customer may return more than once per week.

I also want to Grand Total at the end. DistinctCount doesn't work here, either, for the same reason.

Any ideas?

Thanks

 

[kenhamady]

You can do this with a running total if you use the 3-formula technique. See the FAQ on running totals for an example.

To make sure that the formula only picks up once per provider, put the formula that does the assignment in the group footer for the provider.

You will need one running total set for the grand total (no reset formula needed) and another 3-formula set to do the weeks. Reset this in the Group header of the week. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)