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