Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
i have a excel file which contain 2 field named Date and Transaction
now i want to show daily Opening balance and closing balance
Opening Balance= in first day it is Zero and from second day it will be Last day closing balance
Closing Balance= Today's opening+today's Transaction
plz suggest me what expression is used.
and data is..
Try to create one Flag for Issue and Receipt.. If it is issue then subtract that transaction quantity..
Opening balance
Only({<date={<$(=today ()-1>}>}closingbal)
Cloding balance
Only({<date={<$(=today ()-1>}>}closingbal)+
Sum ({<date={$(=today ())}>}credits)-
Sum ({<date={$(=today ())}>}debits)-
Hi,
one solution could be:
LOAD *,
Alt(Peek([Closing Balance]),0)+[Tran Amount] as [Closing Balance],
Alt(Peek([Closing Balance]),0) as [Opening Balance]
FROM [Total%20Tran.xls]
(biff, embedded labels, table is Sheet1$);
(If there might be more than one row per day, you would have to include some aggregation (Sum) and grouping per day)
hope this helps
regards
Marco
Hi,
one solution could be:
LOAD *,
Alt(Peek([Closing Balance]),0)+[Tran Amount] as [Closing Balance],
Alt(Peek([Closing Balance]),0) as [Opening Balance]
FROM [Total%20Tran.xls]
(biff, embedded labels, table is Sheet1$);
(If there might be more than one row per day, you would have to include some aggregation (Sum) and grouping per day)
hope this helps
regards
Marco
edit: Test without URL due to moderation status
----------------------------------------------------------------------------
Hi,
one solution could be:
LOAD *,
Alt(Peek([Closing Balance]),0)+[Tran Amount] as [Closing Balance],
Alt(Peek([Closing Balance]),0) as [Opening Balance]
FROM [Total Tran.xls] (biff, embedded labels, table is Sheet1$);
(If there might be more than one row per day, you would have to include some aggregation (Sum) and grouping per day)
hope this helps
regards
Marco
Hi,
please close this thread if there are no further questions.
Thanks
Regards
Marco