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

[cpjeffm]

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...

if {CusDocData.DocDate} < {?Detail Start Date} then
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!

 

[Charliy]

Use a Running Total
Sum the field
on Condition {table.date} < {?parameter date}

 

[cpjeffm]

Already tried that. Read the last 3 lines of the original post.

 

[kray4660]

I have never known a running total to 'fail'. I think a closer examination of the data is in order. Make sure each detail has the data you are expecting.

 

[cpjeffm]

There are 7 invoices with a balance due for the customer I'm looking at...all of which have a CusDocDate present in the data. 3 of the 7 have a CusDocDate less than 5/1/16 and 4 have a CusDocDate greater than or equal to 5/1/16.

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} then
sum({CusDocData.InvoiceBalance},{CustomerData.AccountNum})

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

I'm at a loss.

 

[AndyMc]

I don't think you want the result of your if-then formula to be a sum:

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.

 

[cpjeffm]

That did it! Thanks a ton. Still can't figure out why the running total didn't work but as long as one of them did, I'm good.