Logic help to calculate opening stock and closing Stock.

[lalitkgehlot89]

Hi ,

I my data I have day wise Total Received and Total dispatch against each vendor.

and a single Opening_Bal figure for each Party.

From the data I want to calculate Closing Stock and Opening Stock for each day and each party.

logic for Closing Stock :- Opening_Bal  +  TotalReceived  -  TotalDispatch

logic for Opening Stock :-  Last day Closing Stock will be today Opening Stock,

Example :-

     Closing Stock of 01/01/2018 = Opening Stock for 01/01/2018  + TotalReceived  on 01/01/2018   -  TotalDispatch on 01/01/2018

     Opening Stock for 02/01/2018 = Closing Stock of 01/01/2018

      Closing Stock of 02/01/2018  =  Opening Stock for 02/01/2018  + TotalReceived  on 02/01/2018   -  TotalDispatch on 02/01/2018

      Opening Stock for 03/01/2018 = Closing Stock of 02/01/2018

      Closing Stock of 03/01/2018  =  Opening Stock for 03/01/2018  + TotalReceived  on 03/01/2018   -  TotalDispatch on 03/01/2018

     and soon..

Note :- Opening Stock for first date is in data for every Party.

Please help me out to right the logic for the same.

Thanks,

Lalit

[balabhaskarqlik]

May be this:

=(sum({<Date = {'=$(Max(Date)-1)'}>}OpeningBal) + sum({<Date = {'=$(Max(Date)-1)'}>}StockReceived) - sum({<Date = {'=$(Max(Date)-1)'}>}StockDispatch)) + sum({<Date = {'=$(Max(Date))'}>}StockReceived) - sum({<Date = {'=$(Max(Date))'}>}StockDispatch)

[rubenmarin]

Hi Lalit, you can complete the data using this script:

tmpStock:
LOAD Party,
    Date,
    TotalDispatch,
    TotalReceived,
    Opening_Bal,
    Closing
FROM
.\Stock.xls
(biff, embedded labels, table is Sheet1$);

Stock:
NoConcatenate
LOAD
Party,
Date,
TotalDispatch,
TotalReceived,
Opening_Bal,
RangeSum(Opening_Bal, -TotalDispatch, TotalReceived)
as Closing
;
LOAD
Party,
Date,
TotalDispatch,
TotalReceived,
If(IsNull(Opening_Bal) and Peek(Party)=Party
  , Peek(Closing)
  , Opening_Bal) as Opening_Bal
Resident
tmpStock
Order By
Party, Date
;

DROP Table tmpStock;

[thannila]

Table:

LOAD

    Product,

    monthname([Date]) as MonthYear,

    day(monthend(Date)) as no.of.days,

    sum("Sale Value") as sales,

    sum( Cost)as cost,

    sum("In Qty") as INQTY,

    SUM("Out Qty") AS OUTQTY,

    sum("In Qty")-SUM("Out Qty") AS STOCKINHAND,

    NUM(month(date#(monthname([Date]) ,'MMM YYYY'))) as MonthNumber

FROM [lib://d/inventory.xlsx]

(ooxml, embedded labels, table is Sheet1) group by  monthname([Date]),Product,day(monthend(Date));

Table2:

load  *,IF(Product=PREVIOUS(Product),RANGESUM(STOCKINHAND,PEEK('CB')),STOCKINHAND) as CB,

IF(Product=PREVIOUS(Product),PEEK('CB'),0) as OB,

Resident Table;

drop table Table;