I am calculating DaysOfSupply for the following database in which i have data table as Follows:
For Days of supply Calculation I need to track on the productId ,LocationId and next days forecast Stock,
Like for ProductId 1 and LocationId 1 ,projectedStock for 1/1/2013 is 2861 now this projected stock need to compare with next days forecast stock which is of 2/1/2013 is 4901
Rule (a)so here my projectedstock<nextdaysForecastStock so ProjectedStock/nextdaysForecastStock
Similarly next case will arrive if projectedstock>=nextdaysForecastStock
then the steps involved is:
1) Calculate the difference between projectedstock-nextdaysForecastStock as StockDiff and set counter as 1again this StockDiff
will be compared by twodaysAfterForecastStock .
2)if StockDiff >=twodaysAfterForecastStock then follow step 1 again and increase the counter by 1 again every time in the loop the StockDiff act as projectedStock and (n)daysAfterForecastStock act as nextdaysForecastStock ,
3)else if StockDiff <(n)daysAfterForecastStock then (a) Rule will followed and the last got counter will be added with this ProjectedStock/nextdaysForecastStock quantity and the counter will be taken as DaysOfSupply.
How can this be done, I am attaching my file in which i am working