topic Re: hi in QlikView

hi

Wed, 25 Jun 2014 08:52:03 GMT

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

Re: hi

selvakumarsr — Wed, 25 Jun 2014 08:56:35 GMT

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

Re: hi

Wed, 25 Jun 2014 08:59:56 GMT

Opening balance

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

Re: hi

Wed, 25 Jun 2014 09:03:52 GMT

Cloding balance

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

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

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

Re: hi

MarcoWedel — Wed, 25 Jun 2014 10:52:43 GMT

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

Re: Re: hi

MarcoWedel — Wed, 25 Jun 2014 11:04:36 GMT

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

Re: hi

MarcoWedel — Wed, 25 Jun 2014 11:06:33 GMT

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

Re: hi

MarcoWedel — Wed, 09 Jul 2014 12:21:11 GMT

Hi,

please close this thread if there are no further questions.

Thanks

Regards

Marco