# New to QlikView

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

Announcements
cancel
Showing results for
Did you mean:
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
MVP

Hi,

maybe one solution could be:

```table1:
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

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

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

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

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

MVP

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

thanks

regards

Marco

Master III

table1:

Month,

Production,

Sell

final:

Month,

Production,

Sell,

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

Resident table1

Order by Month

;

Drop table table1;