Qlik Community

QlikView Documents

Documents for QlikView related information.

Inventory Balances

andreasasenterp
New Contributor

Inventory Balances

Hello everyone!

I want to talk about Inventory Balance. How we can count curent Balance when we know Opening Balance and Periodic operation.

For example, we have curent debt on 01.01.2017:

OpeningBalance.png

And you have operation list:

OperationList.png

There is and operation which show "in" and "out" payments in list of operation. We need to know Opening and Closing balance on every day.

Lets do it!

tmpOperation:

LOAD Date(Date) as Date,

     ID,

     Amount

FROM

[..\OperationList.xls]

(biff, embedded labels, table is Sheet1$);

Left Join (tmpOperation)

LOAD ID,

     OpeningBalance

FROM

[..\Остатки задолженности.xlsx]

(ooxml, embedded labels, table is Лист1);

Operation:

LOAD Date,

     ID,

     Sum(Amount) as DayBalance,

     Max(OpeningBalance) as OpeningBalance

Resident tmpOperation

Group By ID, Date

Order By ID, Date

;

DROP Table tmpOperation;

InventoryBalance:

LOAD Date,

     ID,

     If(IsNull(Previous(OpeningBalance)) or ID<>Previous(ID),OpeningBalance,Peek(ClosingBalance)) as OpeningBalance,

     DayBalance,

     If(IsNull(Previous(OpeningBalance)) or ID<>Previous(ID),OpeningBalance+DayBalance,Peek(ClosingBalance)+DayBalance) as ClosingBalance

Resident Operation

Order By ID, Date

;

DROP Table Operation;

After that we have table like this:

InventoryBalance.png

Attachments
Version history
Revision #:
1 of 1
Last update:
‎07-07-2017 10:05 AM
Updated by: