Sum If-Then Formula based on RT 1

[Krickles]

Report purpose: calculate variable incentive

Problem: can't sum my formula field even though it displays as numeric when hovering over it and I have tried converting the results with ToNumber.

Current display:

GH#1: Order_Plant
GH#2: Employee
GH#3: Ship_Date
Detail(3 fields): Ticket_Number | [green]#LoadCount[/green] | [red]Payment[/red]
GF#3: Ticket Count
GF#2: Suppressed
GF#1: Suppressed

Definitions:[ul][li][green]#LoadCount[/green] --> Running Total count of Ticket_Number evaluated for each record and resetting on change of Group 3 (Ship_Date)[/green][/li]
[li][red]Payment[/red] -->
[tt]IF ToNumber({#LoadCount}) < 4 THEN ToNumber (1.25)
ELSE IF ToNumber({#LoadCount}) = 4 THEN ToNumber (5.00)
ELSE IF ToNumber({#LoadCount}) = 5 THEN ToNumber (7.50)
ELSE IF ToNumber({#LoadCount}) > 5 THEN ToNumber (10.00)[/tt][/li][/ul]

Desired display:

GH#1: Order_Plant
GH#2: Employee
GH#3: Ship_Date
Detail(3 fields): Ticket_Number | #LoadCount | Payment
GF#3(2 fields): Ticket Count | SUM(Payment)
GF#2(1 field): Average(Payment)
GF#1: Suppressed

Example:

GH#1: [blue]Plant 12[/blue]
GH#2: [blue]Krickles[/blue]
GH#3: [blue]10-10-2003[/blue]
Detail:
[blue]011 | 1 | $1.25[/blue]
[blue]023 | 2 | $1.25[/blue]
[blue]025 | 3 | $1.25[/blue]
[blue]041 | 4 | $5.00[/blue]
[blue]055 | 5 | $7.50[/blue]
GF#3: [blue]5 | $16.25[/blue]
GF#2: [blue]$3.25[/blue]
GF#1: Suppressed

 

[Ngolem]

Well I would do a manual sum myself (I don't like using Summaries if I can avoid it....just me)

So you are using summary formulas for SUM and Average

I would do it this way

In the Group 2 header I would place this formula

//@Initialize

WhilePrintingRecords;

If not InrepeatedGroupHeader then
(
numberVar totalAmt := 0;
numberVar GrandTotal := 0;
numberVar totalTicket := 0;
);

Now in the details I would leave your running total alone but use a different formula for Payment

//@Payment

WhilePrintingRecords;
numberVar totalAmt;
numberVar temp;

IF {#LoadCount} < 4 THEN temp := 1.25
ELSE IF {#LoadCount} = 4 THEN temp := 5.00
ELSE IF {#LoadCount} = 5 THEN temp := 7.50
ELSE IF {#LoadCount} > 5 THEN temp := 10.00;

totalAmt := totalAmt + temp;
&quot;$&quot; + totext(temp,2);

Now for the display of the Sum in Group 3 I would do this formula

//@DisplaySum

WhilePrintingRecords;
numberVar totalAmt;
numberVar GrandTotal ;
numberVar totalTicket;
numberVar temp;

temp := totalAmt;
GrandTotal := GrandTotal + temp;
totalTicket := totalTicket + {#LoadCount};
totalAmt:= 0;

&quot;$&quot; + totext(temp,2);

For the display of the average I would do this

//@average

WhilePrintingRecords;
numberVar GrandTotal ;
numberVar totalTicket;

if totalTicket > 0 then
&quot;The average value: $&quot; + totext(GrandTotal/totalTicket,2)
else

&quot;The average value: $0.00&quot; ;

that should do it...I usually don't use running totals either if they give a problem here they are easily replaced

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[Krickles]

Bingo! Thanks.

Krickles | Ô ~ 1.6180