Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor
Contributor

How to get the stock value from previous row .

I have production and sale of a product for the current months and the stock for the previous months.I have to forecast for future months and I have the forecasted Production and sell with my so NOw I want to calculate the stock for all the months and the formula is

ForecastedMonthProduction + PreviousMonthStock - ForecastMonthSell.

Let me give an example of how the data is

MonthProductionSellStock
3/1/20181005050

4/1/2018

5050

Need to calculate

(currentMonthProduction -

current Monthsell +

Previous month Stock)

5/1/201840040Need to calculate
6/1/201830100Need to calculate
7/1/20183545Need to calculate

Thanks for the help by the way I can tell Peek, the Previous function will not work here.

Please let me know if you n

7 Replies

Hi,

maybe one solution could be:

QlikCommunity_Thread_296687_Pic1.JPG

table1:

LOAD Month,

    Production,

    Sell,

    Alt(Stock,RangeSum(Peek(Stock),Production,-Sell)) as Stock

FROM [https://community.qlik.com/thread/296687] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco

Contributor
Contributor

Hi Macro,

Thanks for the help but unfortunately this is not working, because of the way peek function works it takes the previously loaded value, not the value which comes after calculation. This is the challenge we have.I don't have the value available for the future months. I have the stock data available the current month.

That is the reason I wrote Peek and the previous function might not work.

Regards,

Lucky Gupta

MVP
MVP

I hope that you have not just seen that peek() has been used and decided that 'this is not working'. It seems that marcowedel‌'s solution gives the output you wanted. I would suggest to look at the solution given once again; if seen that is not what you want, try to explain what is that - did not work, and you exactly want.

Creator
Creator

Hi Lucky

I think, you need to calculate a new column as PreviousMonthStock so that you calculate stock as simply ForecastedMonthProduction + PreviousMonthStock - ForecastMonthSell.

This can be done as what we call Carry-Forward logic:

Step 1: Populate data for all possible dates/months. So if there are no entries then you've to put zeroes, which can be done by generating a table with all possible combinations of dimensions(including Date/Month) for which you're calculating.

2. Sort by all dimensions like order by dim1, dim2, ....Month;

3. Calculate Peek(PreviousMonthStock ,-1,Peek(Stock,-1))as PreviousMonthStock

..something like that, I forget exact syntax.

Contributor
Contributor

HI Tresesco,

I tried what Macro shared with me and then I am said it's not working.

I will look back into it

Regards,

Lucky Gupta

Please post your expected result (the missing values of your "Stock" column) based on the sample data you provided.

thanks

regards

Marco

Master III
Master III

table1: 

LOAD

     Month, 

    Production, 

    Sell

FROM your excel;


final:

Noconcatenate Load

Month,

Production,

Sell,

(Production-Sell+If(rowno()=1,0,Peek(Stock))) as Stock

Resident table1

Order by Month

;


Drop table table1;