Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi People:
I need calculate initial balance for accounts, with this structure.
The Data is: | ||||||
Year | Month | Date | Account | Debit | Credit | |
2010 | 4 | 01/04/2010 | E100 | 500 | ||
2010 | 4 | 09/04/2010 | E100 | 300 | ||
2010 | 5 | 08/05/2010 | E100 | 400 | ||
2010 | 5 | 16/05/2010 | E101 | 120 | ||
2010 | 7 | 11/07/2010 | E200 | 80 | ||
2010 | 7 | 19/07/2010 | E100 | 30 | ||
2010 | 8 | 25/08/2010 | E200 | 140 | ||
And need show .. | ||||||
Year | Month | Account | beginning balance | Debit | Credit | book balance |
2010 | 4 | E100 | 0 | 500 | 300 | 200 |
2010 | 5 | E100 | 200 | 520 | 0 | 720 |
2010 | 7 | E100 | 720 | 0 | 30 | 690 |
Total | E100 | 0 | 1020 | 330 | 690 | |
2010 | 5 | E101 | 0 | 120 | 0 | 120 |
Total | E101 | 0 | 120 | 0 | 120 | |
2010 | 7 | E200 | 0 | 80 | 0 | 80 |
2010 | 8 | E200 | 80 | 140 | 0 | 220 |
Total | E200 | 0 | 220 | 0 | 220 | |
Grand Total | 0 | 1360 | 330 | -1690 |
Is possible? any idea?
Thanks! regards
César Estrada
I think you should be able to use Inter-record functions to do it. Once you take the date field out, the Year and Month will be grouped, so your Debit and Credit expressions should work as is.
For Beginning Balance, it would be something like:
Above(Debit) - Above(Credit) + Above(BeginningBalance)
You may also be able to use Accumulation and have the Beginning Balance field be:
Above(Debit) - Above(Credit)
EDIT: Actually, the first expression should probably be:
Then set your Null Symbol to 0.Above(Debit) - Above(Credit) +
If(Above(BeginningBalance) > 0, Above(BeginningBalance), 0)
Cesar,
See attached solution.
Regards,
Now try with this tips, Thanks!!
Thanks!! work perfect!!