Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

tabisen1
New Contributor

Calculation for the next day opening stock

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

Loaded tables in qv.PNG

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

Result want.PNG

1 Solution

Accepted Solutions
fvelascog72
Valued Contributor

Re: Calculation for the next day opening stock

Hi,

Replace this:

If (OpeningQTY = '', null(), OpeningQTY)as OpeningQTY,

With:

If (OpeningQTY = '' or OpeningQTY = 0, null(), OpeningQTY)as OpeningQTY,
18 Replies
FrankHartmann
Honored Contributor

Re: Calculation for the next day opening stock

see attached file for frontend solution

hope this helps

fvelascog72
Valued Contributor

Re: Calculation for the next day opening stock

Hi,

If you want to do it in script...

tabisen1
New Contributor

Re: Calculation for the next day opening stock

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
New Contributor

Re: Calculation for the next day opening stock

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
Valued Contributor

Re: Calculation for the next day opening stock

You can group by fields you want before peeking values.

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

FrankHartmann
Honored Contributor

Re: Calculation for the next day opening stock

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
New Contributor

Re: Calculation for the next day opening stock

Hi

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

tabisen1
New Contributor

Re: Calculation for the next day opening stock

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
New Contributor

Re: Calculation for the next day opening stock

result.PNG

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

group by.PNG

Community Browser