## Sum a field if a date field is less than a date parameter

## Sum a field if a date field is less than a date parameter

(OP)

Using Crystal Reports 14 and working on a report for accounting. The two classes I'm using are CusDocData and CustomerData. The report is grouped on the CustomerData.AccountNum field.

I have a date parameter called "Detail Start Date" and I need to sum the CusDocData.InvoiceBalance field if the CusDocData.DocDate is less than the "Detail Start Date" and place it in the Group header to provide a balance forward amount. I'm using 5/1/16 as my Detail Start Date and I should get a total invoice balance of $610.00. I've tried a formula like this...

...but it shows the entire account balance of $675.48, not just the balance before the 5/1/16.

And I've tried a running total summing the CusDocData.InvoiceBalance field using a formula of

Any help with this would be greatly appreciated!

I have a date parameter called "Detail Start Date" and I need to sum the CusDocData.InvoiceBalance field if the CusDocData.DocDate is less than the "Detail Start Date" and place it in the Group header to provide a balance forward amount. I'm using 5/1/16 as my Detail Start Date and I should get a total invoice balance of $610.00. I've tried a formula like this...

*if {CusDocData.DocDate} < {?Detail Start Date} then*

sum({CusDocData.InvoiceBalance})sum({CusDocData.InvoiceBalance})

...but it shows the entire account balance of $675.48, not just the balance before the 5/1/16.

And I've tried a running total summing the CusDocData.InvoiceBalance field using a formula of

*{CusDocData.DocDate} < {?Detail Start Date}*and on a change of group CustomerData.AccountNum but the total shows only the invoice balance of the first invoice in sequence prior to 5/1/16 which is $525.00. It's not picking up the other two invoices with a DocDate less than 5/1/16.Any help with this would be greatly appreciated!

## RE: Sum a field if a date field is less than a date parameter

Sum the field

on Condition {table.date} < {?parameter date}

## RE: Sum a field if a date field is less than a date parameter

## RE: Sum a field if a date field is less than a date parameter

## RE: Sum a field if a date field is less than a date parameter

All 7 invoices have a total balance due of $675.48.

The 3 invoices with a date less than 5/1/16 have a total balance due of $610.00

Invoice# 45 / DocDate 4/19/16 / Balance $525.00

Invoice# 178 / DocDate 4/22/16 / Balance $5.00

Invoice# 189 / DocDate 4/28/16 / Balance $80.00

The 4 invoices with a date greater than or equal to 5/1/16 have a total balance due of $65.48

Invoice# 209 / DocDate 5/3/16 / Balance $5.00

Invoice# 212 / DocDate 5/4/16 / Balance $5.00

Invoice# 214 / DocDate 5/4/16 / Balance $5.00

Invoice# 217 / DocDate 5/4/16 / Balance $50.48

I revised my formula but it still shows $675.48. The new formula is...

if {CusDocData.DocDate} < {?Detail Start Date} thensum({CusDocData.InvoiceBalance},{CustomerData.AccountNum})

The running total shows $525.00 which is the first invoice# 45 listed above.

I'm at a loss.

## RE: Sum a field if a date field is less than a date parameter

Instead put the formula (without the 'sum' part) in the detail section and then sum that formula:

if {CusDocData.DocDate} < {?Detail Start Date} then {CusDocData.InvoiceBalance} else 0

Sum this formula and put the result in the header group and suppress the detail formula.

## RE: Sum a field if a date field is less than a date parameter