hi

[#1]

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

[selvakumarsr]

Try to create one Flag for Issue and Receipt.. If it is issue then subtract that transaction quantity..

[#3]

Opening balance

Only({<date={<$(=today ()-1>}>}closingbal)

[#4]

Cloding balance

Only({<date={<$(=today ()-1>}>}closingbal)+

Sum ({<date={$(=today ())}>}credits)-

Sum ({<date={$(=today ())}>}debits)-

[MarcoWedel]

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

[MarcoWedel]

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

[MarcoWedel]

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

[MarcoWedel]

Hi,

please close this thread if there are no further questions.

Thanks

Regards

Marco