Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tabisen1
Contributor II
Contributor II

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
Partner - Specialist
Partner - Specialist

Hi,

Replace this:

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

With:

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

View solution in original post

18 Replies
Frank_Hartmann
Master II
Master II

see attached file for frontend solution

hope this helps

fvelascog72
Partner - Specialist
Partner - Specialist

Hi,

If you want to do it in script...

tabisen1
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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
Partner - Specialist
Partner - Specialist

You can group by fields you want before peeking values.

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

Frank_Hartmann
Master II
Master II

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
Contributor II
Contributor II
Author

Hi

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

tabisen1
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

result.PNG

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

group by.PNG