Creating a “Distinct Sum” when the duplicates not grouped together

Sunday 24 November 2019 @ 3:36 pm

Crystal has a distinct count function but not a distinct sum. A distinct sum would be a sum that skips values in one field whenever there is a duplicate in a separate “key” field. It would work something like this: “only add each customer’s balance into the total once – even if a customer shows up in several different places in the report. The customer ID would be the “key” field while the balance would be the field you are totaling. It should only count the value on the first instance of the key field.

If you can group the duplicates together the solution is simpler. You can use a running total and set it to “evaluate on change of group” where the group is the key field. I teach this method in my advanced material. But a long ago student showed me a clever way to identify duplicates, even when they were scattered. I had never seen that method and couldn’t find it described anywhere else. I published it in my newsletter in 2004. When I needed to use it last week I decided to post it here.

Last week my challenge was a payroll report that showed employees grouped by department, showing pay and withholding. Pay was split by Dept but the withholding was combined. So when an employee worked in two different departments, his withholding would show up twice in the totals. I needed the totals to count the withholding once per employee, even if the employee showed up in several departments (on different pages).

To use this method you first create a running total field that is a distinct count of the “key” field. Then you create another running total, this time using variables, to total the numeric field. This formula has logic to only add the value when the first running total has changed from the record before. Any time there is a change in the first running total it must mean there is a new value for the “key” field. When the first running total doesn’t change it means that the “key” field value has appeared before. The formula I used looks like this:

NumberVar Prior;
NumberVar LYS;
if {#CustCount} = Prior + 1 //test if first record for this customer
then LYS := LYS + {Customer.Last Year's Sales};
Prior := {#CustCount}; //store current count to use for next record

If you need a Distinct Sum as a Subtotal you would reset the running total and both of the variables with each group.

I have seen other developers solve this problem by creating an array of all of the “key” values and checking each “key” value against the array before adding the numeric. This method lets Crystal handle that duplicate check so there is no reason to maintain an array.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)

Leave a Reply

Recrystallize Pro

Crystal Reports Server