I have two column totals.

 ON TABLE COLUMN-TOTAL AS 'TOTAL' totearlyinvamt totlateinvamt 

The totearlyinvamt is coming out correct but the totlateinvamt is not. In fact it isn't even close. Here are the column definitions.

  totearlyinvamt/D12.2CSM AS 'Invoice Amount,Paid Early'
     totlateinvamt/D12.2CSM AS 'Invoice Amount,Paid Late'

Here are the defines for each.

  IF paydate1 LE DUE_DATE THEN totearlyinvamt + GROSS_AMOUNT ELSE 0

 IF paydate1 GT DUE_DATE THEN totlateinvamt + GROSS_AMOUNT ELSE 0 

Can anyone help with this?

This message has been edited. Last edited by: Tracie,

Are Are you sure the individual amounts are correct in totlateinvamt? Are you capturing everything that should be in that define? If the Total is incorrect...then I would imagine it is the individual amounts that are incorrect.

The totals are correct. The totlateinvamt starts out as correct. Invoice amt $150 on the first line/totlateinvamt = "$150. Second line invoice amt is $200/totlateinvamt is $350 and so on. The number is correct until it hits the first line contains an paid early amount.

Here are the first two lines of the report

$89,732.47 total late $89,732.47 
$42,570.50 total late $132,302.97 

It keeps adding this way until::

  $4,410.40 total late $371,442.90 
$22,391.86 total early $22,291.86  
$3,813.10 total early $26,204.96   
$25,106.61 total early $51,311.57   
$10,997.00 total early $62,308.57   
$5,195.40 total late $5195.30 
$67,175.58 total early $67,175.58   
$2,484.58 total late $2,484.58 
                    
Total early  $229,392.54 This number is correct.
Total late $3,845,744.34 (should be 379,122.88) 

Is the date comparison working?

IF paydate1 GT DUE_DATE

Yes, the date comparison is working. The ones that should be early are and the ones late are showing as late.

Could you give us all of your code? I am not even seeing how you are using column-total. From your example, it appears like you are creating a running total, not a column-total.

 PRINT 
     SERIES_ID AS 'Invoice Type'
     GROSS_AMOUNT/D20.2CM AS 'Invoice,Amount'
     INVOICE_DATE/MDYY AS 'Invoice Date'
     DUE_DATE/MDYY AS 'Due Date'
     paydate1/MDYY AS 'Date Paid'
     diff AS 'Days ,Behind/Ahead'
     posneg/A40 AS ''
     totearlyinvamt/D12.2CSM AS 'Invoice Amount,Paid Early'
     totlateinvamt/D12.2CSM AS 'Invoice Amount,Paid Late'
BY INVOICE_NO AS 'Invoice No.'
HEADING
"Customer No:  <+0>&IDENTITY<+0>    <NAME "
FOOTING
"Prepared on: <+0>&DATEtrMDYY <+0>        "
" Page:<TABPAGENO"
ON TABLE SUBFOOT
"Total number of invoices paid late:  <late      Total number of invoices paid early:   <early "
" Total number of invoices:     <totalinvoices  "
WHERE ( IDENTITY EQ '&IDENTITY.Customer No:.' ) AND ( STATE EQ 'PaidPosted' );
WHERE SERIES_ID EQ 'CD';
WHERE GROSS_AMOUNT GT 0;
WHERE ( PAY_DATE GE DT(&FromPaidDate.From Date Paid.) ) AND ( PAY_DATE LE DT(&ToDatePaid.To Date Paid.) );
ON TABLE SET PAGE-NUM OFF 
ON TABLE COLUMN-TOTAL AS 'TOTAL' totearlyinvamt totlateinvamt
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE * 

Here you go. Thanks for the help.

I think there are some things missing. Where are your defines? You also reference fields in you subfoot lines that don't appear anywhere. Are they also in the defines?

Without seeing all of the code, here are a couple suggestions: It appears that totearlyinvamt and totlateinvamt are running totals. This would not appear correctly if you are trying to add a column-total to a running total column. Also, I usually use

ON TABLE SUBTOTAL AS 'TOTAL'
totearlyinvamt
totlateinvamt

 IF paydate1 LE DUE_DATE THEN totearlyinvamt + GROSS_AMOUNT ELSE 0 

 IF paydate1 GT DUE_DATE THEN totlateinvamt + GROSS_AMOUNT ELSE 0 

Here are the defines. The first one for totearlyinvamt and the second one is for totlateinvamt. I tried your suggestion and got the same results.

This message has been edited. Last edited by: Tracie,

Tracie

Your defines of the totearlyinvamt and totlateinvamt should be done in the table part and not as predefined fields.

How do I create these fields in the Table part using the report painter? Thank you.

quote:

IF paydate1 LE DUE_DATE THEN totearlyinvamt + GROSS_AMOUNT ELSE 0
IF paydate1 GT DUE_DATE THEN totlateinvamt + GROSS_AMOUNT ELSE 0

This would not be correct syntax for a define. By this, do you mean:
DEFINE FILE xxx
totearlyinvamt/D12.2=IF paydate1 LE DUE_DATE THEN totearlyinvamt + GROSS_AMOUNT ELSE 0 ;
totlateinvamt/D12.2=IF paydate1 GT DUE_DATE THEN totlateinvamt + GROSS_AMOUNT ELSE 0;
END

Why are you adding totearlyinvamt back onto itself? And, as this is calculated record by record in the define, that amount will always be zero so it would be the same as

totearlyinvamt/D12.2=IF paydate1 LE DUE_DATE THEN GROSS_AMOUNT ELSE 0;
totlateinvamt/D12.2=IF paydate1 GT DUE_DATE THEN GROSS_AMOUNT ELSE 0;

So for each record, it should have either one amount or the other, but not both, correct?
so try

DEFINE FILE XXX
totearlyinvamt/D12.2=IF paydate1 LE DUE_DATE THEN GROSS_AMOUNT ELSE 0;
totlateinvamt/D12.2=IF paydate1 GT DUE_DATE THEN GROSS_AMOUNT ELSE 0;
END
TABLE FILE XXX
PRINT 
     GROSS_AMOUNT
     INVOICE_DATE
     DUE_DATE
     totearlyinvamt
     totlateinvamt
BY INVOICE_NO 
ON TABLE SUBTOTAL
END

and see what you get.

There are still other pieces of your code that you are not showing that could have some impact on what you are seeing. Without seeing all of it, it's hard to be very exact with a solution.

JI - too early in the morning - you do have a profile - sorry about that - Michelle

From the description in your second post

quote:

The number is correct until it hits the first line contains an paid early amount

I come to the conclusion that you need to have a kind of running total for the early and late invoices. If that's the case, then the total values in the ON TABLE SUBTOTAL do not make any sense to me. But that's beside the issue at hand. If you need to have this running total continue even if the date changed back and forth, code the defines as:

IF paydate1 LE DUE_DATE THEN totearlyinvamt + GROSS_AMOUNT ELSE totearlyinvamt  
IF paydate1 GT DUE_DATE THEN totlateinvamt + GROSS_AMOUNT ELSE totlateinvamt

Now the total values of the early/late invoice stay intact when the date changes, in stead of being reset to 0.

Hope this helps...

That solution worked. Thanks