I want to calculate opening and closing for inventory Stock in hand

[ferha_jafri]

Hello everyone,

I am having an issue in calculating the opening and closing stock at the end of the day. we are developing inventory reports,here at the back end the kept the stock in summary table till 30-sep-2013 and kept the transaction in detail table. I want to calculate opening and closing for each day .I have a sample of the situation please provide me the solution if anyone have.

Thanks in advance

Ferha Jafri

[ferha_jafri]

Please reply asap it is urgent.

[tresesco]

For opening, may be like :

Load

Peek('Inventory Detail') AS Opening_Inventory

Closing? If you have a single value for a date that itself is closing, isn't it?

PFA

[ferha_jafri]

what client did they made a summary table in which the sum the stock till 30-sep-2013 and kept there and transaction start from 1-oct-2013 in detail table so final stock will be summarized stock + stock in hand for one day in detail table.

as if summary has 2000 stock so for 1-oct-2013 stock will be 2000-10=1990 and so on so in this case 1990 will become new opening for next day.

[tresesco]

Hope, I have understood now. Try like:

Load *,

  If(RecNo()=1, $(vInventorySum)-[Inventory Detail], Peek('OpeningStock')-[Inventory Detail]) AS OpeningStock
  ;

PFA

[tresesco]

Did a correction:

Load *,

  If(RecNo()=1, $(vInventorySum), Peek('OpeningStock')-[Inventory Detail]) AS OpeningStock,
  If(RecNo()=1, $(vInventorySum), Peek('ClosingStock')-[Inventory Detail])-[Inventory Detail] as ClosingStock
  ;

PFA

Update:One more, correction

[ferha_jafri]

Hi,

I checked the data the output is like opening stock for 01-08-2013 is 1970 and closing is suppose 1910 the 1910 should be the opening stock for 2-8-2013.