How to find opening balance and closing balance in case of any item in invetory

[#1]

Hello everyone,

I am new  in qlikview,

I have a query that how can i find opening and closing balance for each and every month of the year if transaction is available or not in whole month.

means for example,

if in january month opening and closing balance are 100 and 200 respectively.

and in month of feb  there is no transaction and in march there are 50 recieve and 20 issue so Output  should be display like this-

                 opening    closing

jan 2012     100          200

feb 2012     200          200

mar 2012    200          230    ->(200+50-20=230)

Help please as soon as possible

Thanks and i am waiting ......

[varunpbhandary · Accepted solution]

Hi Vishal,

              You can try using the peek function in qlikview after grouping the data monthwise.

Item Ledger:

Load

ITEMID,

ItemDescription,

ItemType,

ItemName,

ItemGroup,

ItemGroupName,

ItemGroupDescription,

YearMon,

BalanceQty,

BalanceAmt,

if(Peek(ITEMID)<>ITEMID,0+BalanceQty,Peek(ClosingQty)+BalanceQty) as ClosingQty,

if(Peek(ITEMID)<>ITEMID,0+BalanceAmt,Peek(ClosingAmt)+BalanceAmt) as ClosingAmt,

if(Peek(ITEMID)<>ITEMID,0,Peek(ClosingQty)) as OpeningQty,

if(Peek(ITEMID)<>ITEMID,0,Peek(ClosingAmt)) as OpeningAmt

Resident ItemTransactionsGrouped Order By ITEMID,YearMon asc;

Hope this helps!

Thanks and Regards,

Varun bhandary