Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Many thanks for this, it created the closing stock as expected.
A nice trick and concept of maths.