INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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!

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

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

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

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

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

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.

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

(OP)
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.

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

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.

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

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close