Concat value based on prev value

[#1]

Hi,

I cant solve this one so im posting my problem here, i think its a no brainer.

I Have this tabel:

BudgetRateDaily = wanted daily % decrease of stock.

daysFromLastReading = days from last reading of stock.

SKU	date	inStock	budgetRateDaily%	daysFromLastReading
Tshirt1	2013-09-01	290	1	1
Tshirt1	2013-10-01	280	1	30
Tshirt1	2013-11-01	260	2	30
Tshirt1	2013-12-01	200	2	30
Tshirt2	2013-09-01	300	3	1
Tshirt2	2013-10-01	250	3	30
Tshirt2	2013-11-01	200	2	30
Tshirt2	2013-12-01	150	2	30

And i want to add de field budgetStock

The thought is to calculate the wanted decrease (budget) by last budgetStock in date order:

SKU	date	inStock	budgetRateDaily%	daysFromLastReading	budgetStock	MY COMMENTS
Tshirt1	2013-09-01	290	1	1	290	First reading,stock and budget is the same
Tshirt1	2013-10-01	280	1	30	230	New budgetStock based on last budgetStock minus 1% daily in 30 days
Tshirt1	2013-11-01	260	2	30	260	The budgetRateDaily is changed and the budgetStock resets to inStock
Tshirt1	2013-12-01	200	2	30	190	New budgetStock based on last budgetStock minus 2% daily in 30 days
Tshirt2	2013-09-01	300	3	1	300	Nother SKU but same logic
Tshirt2	2013-10-01	250	3	30	240
Tshirt2	2013-11-01	200	2	30	200
Tshirt2	2013-12-01	150	2	30	120

Is there any god way?

[jerem1234 · Accepted solution]

I created a script, using your first table as an Inline table. Then I calculated your budgetStock field. Wasn't sure how you calculated budgetStock when the Rate Daily didn't change (you go from 290 - 230 for example). So I based it sort of off your comment to the right. Please find attached.

Hope this helps!

[#3]

thanks jerem1234 for a very useful and clear answer!