×
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!
  • Students Click Here

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

Students Click Here

Jobs

Calculating Opening Balance for amounts less than ?StartDate

Calculating Opening Balance for amounts less than ?StartDate

Calculating Opening Balance for amounts less than ?StartDate

(OP)
thread767-1767157: Sum a field if a date field is less than a date parameter

Please assist me with this:

I am printing a ledger report where the user enters a start and end date. I want to be able to add all (debit - credit) transactions where the transaction date is less than the ?startdate to get the opening balance for each account in the ledger. I have tried several methods without success, and then used the suggestion referenced in this thread, but get a zero amount when printing the report.

1. I created this formula:
if {Transaction.TransactionDate} < {?Start Date} then {Transaction.TransactionDebit} else 0. (Was going to do a another for credits and subtract)
2. I added the formula to the detail line and SUM then added SUM to header and suppressed the deail. Still prints zero.
3. l also want to be able to show the balances for accounts that donot have transactions for the selected periods

I would be most appreciative for assistance with a solution, spent nearly two weeks on this,
Many thanks
bhuven

RE: Calculating Opening Balance for amounts less than ?StartDate

Have you excluded dates earlier than {?StartDate} in your record selection formula? Your technique should work unless you have restricted the report as a whole to a defined start date. What is your record selection formula?

-LB

RE: Calculating Opening Balance for amounts less than ?StartDate

(OP)

Hi, thank you for your response

I have:
{Transaction.TransactionDate} in {?StartDate} to {?End Date} and I sort the date in ascending order

RE: Calculating Opening Balance for amounts less than ?StartDate

Remove the record selection formula. The earlier transactions have to be “IN” the report in order to contribute to the opening balance. Then go to report->selection formula->GROUP and enter:

{transaction.transactiondate}>={?StartDate}

This will allow only the transactions within the time period to display.

Then use the formulas you mention earlier to get the opening balances. If you group on account, you would insert the sum on your formulas at the group level.

Similarly, to get summaries during the current period, use formulas like this:

If {transaction.transactiondate}>={?startdate} then {transaction.transactiondebit}

Then insert at the group level as before.

-LB

RE: Calculating Opening Balance for amounts less than ?StartDate

(OP)
Thank you, will try

RE: Calculating Opening Balance for amounts less than ?StartDate

(OP)
Thank you so much, I am getting figures coming through and need to check balances etc.
To get the end date will I have to do the same like the start date above?

RE: Calculating Opening Balance for amounts less than ?StartDate

(OP)
Thank you LB, works perfectly, much appreciated

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!

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