Calculation for the next day opening stock

[tabisen1]

Dear all,

I am stuck in finding the next day opening stock. Can any one help me to do the following calculation working.

their are three table or file which I have loaded  in the QV

Calculation should

Opening stock + Production - Sales = Closing Stock

Closing Stock will be the opening stock for the next day. same formula for the next day this will go on day wise month or year.

Day end Closing Stock will be next Day opening Stock

Month end Closing Stock will be next month 1st day Opening Stock

Year end Closing Stock Will be next year 1st month 1st day Opening Stock

Result should look like this

[Frank_Hartmann]

see attached file for frontend solution

hope this helps

[fvelascog72]

Hi,

If you want to do it in script...

[tabisen1]

Dear Federico,

It looks fine but the issue is that I am having a multiple line of production with the combination of plant product packing and date. when i want the only opening stock value.

I am getting some issues while running the original file.

error is not peeking the closing stock to opening stock 

[tabisen1]

Dear frank,

your concept is ok but i want to add new column insisted of using above function.

it not add the opening stock after month gets over.

[fvelascog72]

You can group by fields you want before peeking values.

If you have a set of data to play with it.

[Frank_Hartmann]

are you using a mastercalendar or how do you want to add the new date lines?

If im using my above mentioned example and add new month datefield in excel then

the pivottable behaviour is correct in my opinion!

[tabisen1]

Hi

I am trying from group by function but the error is not peeking the next day opening stock value is coming null

[tabisen1]

Dear frank,

I don't want to added any data in the opening stock excel file.

It could be auto update in temp file when ever the production and sales data is updated.

[tabisen1]

As you say use group by pfa how i have used it