Crystal Report - Shared Variables adding last row of data twice in Report Footer

I am using Crystal Reports 2016. I don't do a lot of work with it, and most of what I do, I manage, but have run into a problem that I cannot seem to find a solution to.

As an organisation we have customers who commit to donate a certain amount each month. I have a report that I need to write which shows how many of those customers give more, equal or less than their pledged amount.

I have managed to get around issues with Running Totals not working because of “Sum of” or “Distinct Counts” by using Shared Variables to create running totals. Now however I have hit a snag where the formulas all work as I want them to, with the exception that the Variable adds the last row of data twice in the Report Footer.

I have done various Google searches and tried a range of suggestions, including switching from “WhilePrintingRecords” to “EvaluateAfter” for some of the formulas, but nothing I do seems to change the final counts.

Here are examples of my formulas and their location on the report. Please note, these do not reset so that I get final totals.

Located in Group Footer 1a)

@Pledge vs Actual:

WhilePrintingRecords;
If (Shared CurrencyVar Amount) = {Pledge.field} then ToNumber ("1") // 1 is where pledge and payments are "Equal"
else if (Shared CurrencyVar Amount) > {Pledge.field} then ToNumber ("2") // 2 is where payments are "More" than pledge
else if (Shared CurrencyVar Amount) < {Pledge.field} then ToNumber ("3") // 3 is where payments are "Less" then pledge

This formula is just to determine whether someone has given more, equal or less than their pledge, so that I can then evaluate the data based on that criteria. I have the same formulas as in the examples below for both the “More” and “Less” criteria.

@EqualAmount1:

WhilePrintingRecords;
Shared CurrencyVar EqualValue1;
EqualValue1 := If {@Pledge vs Actual} = 1 then Shared CurrencyVar Amount;

This formula would give a value of, say £50, if the amount donated in the given time period by the relevant customer added up to that amount (summed using the Shared CurrencyVar Amount formula by customer record, which is Group 1).

Located in Group Footer 1b)

@EqualAmount2:

WhilePrintingRecords;
Shared CurrencyVar EqualValue2;
EqualValue2 := EqualValue2 + {@EqualValue1};

This gives a running total of all the customers amounts given so far, e.g. £50 £100 £100 £100 £150 £150 £200 £250

Located in Report Footer

@EqualAmount3:

Shared CurrencyVar EqualValue3;
EqualValue3 := {@EqualValue2};

I thought this would then simply display the final amount achieved through the above formulas, so £250 if that was the last row, but it adds the final row again, making £300 for example.

This is the first time I have ever worked with Variables so am almost definitely missing something very, very simple. As such, any guidance would be greatly appreciated.

---

Update: OK, so having done what heringer suggested, which all works wonderfully; I was able to use the same formula to produce running totals for the pledge amounts as well, but I am now stuck again because I cannot create a Running Total for the Total Amount Donated.

What I mean is that, at the bottom of the report I need to state something along the lines of:

10 customers have given more than their pledge - they pledged £100 and gave a total of £200. 50 customers gave exactly their pledge - they pledged and gave £500. 5 customers gave less than their pledge - they pledged £200 and gave £100.

So, I can use the Running Totals brilliantly for the first two parts of these sentences, but the last bit - the sum of their donations - cannot be done using the same Running Totals formula, as the customer might have multiple order lines, which are summed to show the total given by the customer.

This is what my report looks like so far:

So, for example, if a customer {Sum of @Gross} is £100, it might be made up of 3 lines, 1 of £50 and 2 of £25 each.Using the Running Total just uses the top order line. Sorry, my knowledge if Crystal really is quite limited as I do not use it very often, and only know how to do the basics. :(

I believe there is a "customer" with two donations of $50 in the period and the formula is counting it twice. It's a long shot :)

But, if I understood it correctly, may I suggest another approach?

Create 3 running total fields: CountWhenLess, CountWhenEqual, CountWhenMore. They should count CustomerId and evalute with the folowing formula (change the comparison signal according to each of the 3 cases):

(onfirstrecord or {Customer.CustomerId} <> previous({Customer.CustomerId})) and
Sum ({Incoming.Amount}, {Customer.CustomerId}) < {Customer.Pledge}

The trick is to evaluate when the group changes AND use the comparison formula at the same time.

I made a test. Download the rpt file here, if you wish.

I created this schema to test it:

create table Customer(CustomerId int, Pledge money)
create table Incoming(CustomerId int, Amount money)

insert Customer(CustomerId, Pledge) values (1, 100)
insert Customer(CustomerId, Pledge) values (2, 200)
insert Customer(CustomerId, Pledge) values (3, 300)
insert Customer(CustomerId, Pledge) values (4, 400)
insert Customer(CustomerId, Pledge) values (5, 500)

insert Incoming(CustomerId, Amount) values (1, 60)
insert Incoming(CustomerId, Amount) values (1, 40)
insert Incoming(CustomerId, Amount) values (2, 100)
insert Incoming(CustomerId, Amount) values (2, 110)
insert Incoming(CustomerId, Amount) values (3, 290)
insert Incoming(CustomerId, Amount) values (4, 410)
insert Incoming(CustomerId, Amount) values (5, 490)

The result for this dataset is: less=2, equal=1, more=2 (if I understood it correctly)

EDIT:

You can solve the new issue (see edit of question) with the same approach.

Create more 6 running total fields.

3 running total fieds to sum the pledge (like the previous running total fields, but change the count to sum the pledge value; use the same formula).

3 running total fieds to sum the given amount (like the previous running total fields, but change the count to sum the amount given and change the formula removing the clause that check the change of group).

I have updated the rpt file. Download the rpt file again, if you need more details. I created only 2 new running total fields. I'm sure you may infer the other 4.