Next row value

[#1]

Hello,

I have a spreadsheet which requires the next row of data so that I can calculate the closing stock of the day before (i.e. users only input the opening stock per day and thus I need to look at the next days opening stock to calculate the closing stock of the previous day)

Date	Opening Stock	Closing Stock
26/03/13	2344	5000
27/03/13	5000	6780
28/03/13	6780	9000
29/03/13	9000	1000

So for the above example for the 27/03/13 I currently only have the opening stock of 5000 inputted and I need to look at the 28/03/13 opening stock in order to complete the closing stock for 27/03/13. Obviously peek could be used if I was looking for the previous value but I'm not sure as to how to look at the next field value.

Thanks,

Ralph

[#2]

I want to generate the closing stock from the opening stock, so the closing stock figure will only be generated when the next days opening stock is inputted.

Thanks,

Ralph

[kaushiknsolanki · Accepted solution]

Hi,

     Try this.

Data:
Load Date(Date#(Date,'DD/MM/YYYY')) as Date,Opening_Stock inline [
Date, Opening_Stock
26/03/13, 2344
27/03/13, 5000
28/03/13, 6780
29/03/13, 9000
];

Data1:
Load Date as Date1,Opening_Stock as Opening_Stock1,Previous(Opening_Stock) as ClosingStock
Resident Data Order by Date Desc;

Here because we are sorting the Date in descending order your previous function will act as next.

Regards,

Kaushik Solanki

[#4]

Many thanks for this, it created the closing stock as expected.

[sujeetsingh]

A nice trick and concept of maths.