Opening Balance & Ending Balance [ Trial Balance ]

[DeepakD]

Hi @sunny_talwar  / @maxgro         

I want below output & need to calculate beg balance and ending balance 

could u please help me to achieve this      

Thanks In Advance

Deepak D

Account	date	Description	Beginning Balance ( In Rs)	Debits (In Rs)	Credits (In  Rs)	Ending Balance ( In Rs)
A020101001	31-01-2022	To nostro Credit	0.00	0.00	-200,000,000,000.00	-200,000,000,000.00
A020101001	07-02-2022	To nostro Credit	-200,000,000,000.00	0.00	-200,100,000,000.00	-199,958,082,192.00
A020101001	07-02-2022	To nostro Debit	-200,000,000,000.00	200,141,917,808.00	0.00	-199,958,082,192.00
A020101001	17-02-2022	Nostro/Cash	-199,958,082,192.00	0.00	-100,052,746,460.00	-198,852,966,122.33
A020101001	17-02-2022	To nostro Credit	-199,958,082,192.00	0.00	-99,650,000,000.00	-198,852,966,122.33
A020101001	17-02-2022	To nostro Debit	-199,958,082,192.00	200,291,876,712.00	0.00	-198,852,966,122.33

[rubenmarin]

Hi, you can have the values precalculated using Peek() or Previous() in a sorted load of the data, like:

BalanceData:
LOAD
  Account,
  Date,
  Credit,
  Debit,
  If(Account=Peek(Account)
    ,Peek(EndBalance)
    ,0) as StartBalance,
  If(Account=Peek(Account)
    ,Peek(EndBalance)
    ,0) + Credit - Debit as EndBalance
Resident DataTable
Order By Account, Date;

DROP Table DataTable;

[DeepakD]

Hi @rubenmarin ,

Thanks for the reply So I did same code as u pasted and but getting 0.00  for all rows [ sum(Endbalance)] and ) 0.00 for Startbalance as well. Do I need to create any set expression for opening balance & ending Balance?

Thanks & regards

Deepak D

[rubenmarin]

Hi, the fields should have the values in each row, you can add all fields of that table to a simple table in design and check the values.

Or upload some sample data or app to check what's happening