Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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!
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!
thanks jerem1234 for a very useful and clear answer!