topic Re: How to get the stock value from previous row . in QlikView

How to get the stock value from previous row .

luckygupta72 — Wed, 25 Nov 2020 16:16:04 GMT

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

Month	Production	Sell	Stock
3/1/2018	100	50	50
4/1/2018	50	50	Need to calculate (currentMonthProduction - current Monthsell + Previous month Stock)
5/1/2018	400	40	Need to calculate
6/1/2018	30	100	Need to calculate
7/1/2018	35	45	Need 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

Re: How to get the stock value from previous row .

MarcoWedel — Sun, 01 Apr 2018 20:45:20 GMT

Hi,

maybe one solution could be:

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

Re: How to get the stock value from previous row .

luckygupta72 — Mon, 02 Apr 2018 06:16:23 GMT

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

Re: How to get the stock value from previous row .

tresB — Mon, 02 Apr 2018 06:31:40 GMT

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.

Re: How to get the stock value from previous row .

thevingo — Mon, 02 Apr 2018 06:46:35 GMT

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.

Re: How to get the stock value from previous row .

luckygupta72 — Mon, 02 Apr 2018 06:52:35 GMT

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

Re: How to get the stock value from previous row .

MarcoWedel — Mon, 02 Apr 2018 18:33:08 GMT

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

thanks

regards

Marco

Re: How to get the stock value from previous row .

sasiparupudi1 — Mon, 02 Apr 2018 19:59:50 GMT

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;

Re: How to get the stock value from previous row .

rajeshwar1 — Wed, 19 Jul 2023 18:33:08 GMT

Thanks @sasiparupudi1 . It works.

Re: How to get the stock value from previous row .

gq — Thu, 20 Jul 2023 14:34:09 GMT

If you want the previous record value the way you are expecting, it is important to sort the data by the columns you need and then use peek, that way it does not pick the value from the previous row by load order.

In charts you can use the Above function.