3 Replies Latest reply on Mar 29, 2016 7:21 PM by Vaughan

# Subtraction from a Sum field in a Subsummary part

A donor can give 500 units every 2 months. I want to produce a report each week stating how much each donor can give that week. A Sum field in a Subsummary part based on last name will tell me how much each donor has giving during the specified time period. If I place a Calculated field of "500-Sum" in the Subsummary field with only 1 donor in the found set it will do the calculation properly using only the donations by the specified donor. If I have more than one donor in the found set, it will sum the donation of every single donor even though the Sum field for each donor is correct for that donor.

How can I make the calculation use only the Sum of the donor that the Subsummary names? In the example, Dark Knight should have 50 available and Super Man should have 200 but the program is adding 450+300 and subtracting it from 500.

If only Dark Knight is in the found set the calculation is correct:

My layout looks like this:

Is it possible for the 500-Sum to work like I want or do I need a different strategy?

Thanks.

• ###### 1. Re: Subtraction from a Sum field in a Subsummary part

Assuming that your Sum field already is a summary field, you'll need an additional calculation field with GetSummary() to get the summary field's sub-group value for a sorted found set; like

500 - GetSummary ( sSumOfDonations ; LastName )

where sSumOfDonations is the summary field (Total of), and LastName the field the sub-summary part is sorted by (the two arguments for GetSummary() ).

• ###### 2. Re: Subtraction from a Sum field in a Subsummary part

Correct answer except for one problem. I have a couple of donors with the same last name. They got lumped together under the last name. If I sort by last name and then first name they still get combined. I had to use FirstName in the GetSummary, then have a Subsummary part by LastName followed by a Subsummary part by FirstName. The fields for LastName, FirstName, and VolumeAvailable are in the Subsummary by FirstName. I made the Subsummary by LastName part very narrow so it just looks like a line between donors which is convenient. Thanks for the help.

• ###### 3. Re: Subtraction from a Sum field in a Subsummary part

Donors should have unique ids -- use a serial number.