Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
I am facing problem of calculating the 'days of supply' according to stock on hand . for example
Test:
LOAD * INLINE [
date, Forecast, Production, Reciept, Initial Stock, Stock on Hand
01-01-13, 1217, 795, 707, 600, 885
02-01-13, 1197, 670, 628, 885, 986
03-01-13, 1101, 585, 833, 986, 1303
04-01-13, 1161, 709, 550, 1303, 1401
05-01-13, 1431, 580, 529, 1401, 1079
06-01-13, 1226, 613, 737, 1079, 1203
07-01-13, 794, 717, 796, 1203, 1922
08-01-13, 969, 701, 654, 1922, 2308
09-01-13, 1277, 652, 641, 2308, 2324
10-01-13, 1045, 628, 850, 2324, 2757
11-01-13, 1100, 587, 767, 2757, 3011
12-01-13, 740, 692, 862, 3011, 3825
Expected result of above is as below
date, Forecast, Production, Reciept, Initial Stock, Stock on Hand, Days of Supply
01-01-13, 1217, 795, 707, 600, 885, 0.739
02-01-13, 1197, 670, 628, 885, 986, 0.895
03-01-13, 1101, 585, 833, 986, 1303, 1.099
04-01-13, 1161, 709, 550, 1303, 1401, 0.979
05-01-13, 1431, 580, 529, 1401, 1079, 0.88
06-01-13, 1226, 613, 737, 1079, 1203, 1.42
07-01-13, 794, 717, 796, 1203, 1922, 1.74
08-01-13, 969, 701, 654, 1922, 2308, 1.98
09-01-13, 1277, 652, 641, 2308, 2324, 2.24
The logic of calculating 'Days of Supply' in above script 'Stock on Hand' = 885 we will compare next day of Forecast which is 1197 if
'Stock on Hand' >= Forecast then we consider 'days of supply' =1 .
if 'Stock on Hand' < Forecast then 'days of supply' = 885/1197 which is '0.739' day as usually next day for 02-01-13
'days of supply' = 986/1101 which is '0.895' day .
Now 'Days of Supply' for 03-01-13 is here 1303 is greater than 1161 then 'Days of Supply' = 1+(1303-1161)/1431 which is '1.099'
Please anyone help me . Actually its urgent and I'm not getting that how to solve this problem .Please anyone send me some idea.
Thanks in Advance...
Dear Dhiraj,
Please close this thread by selecting correct answer and create a new thread so that other members can also take interest in new thread.
What wrong you are getting
Temp:
LOAD *, RowNo() as ID INLINE [
date, Forecast, Production, Reciept, Initial Stock, Stock on Hand
01-01-13, 1217, 795, 707, 600, 885
02-01-13, 1197, 670, 628, 885, 986
03-01-13, 1101, 585, 833, 986, 1303
04-01-13, 1161, 709, 550, 1303, 1401
05-01-13, 1431, 580, 529, 1401, 1079
06-01-13, 1226, 613, 737, 1079, 1203
07-01-13, 794, 717, 796, 1203, 1922
08-01-13, 969, 701, 654, 1922, 2308
09-01-13, 1277, 652, 641, 2308, 2324
10-01-13, 1045, 628, 850, 2324, 2757
11-01-13, 1100, 587, 767, 2757, 3011
12-01-13, 740, 692, 862, 3011, 3825
];
FINAL:
Load *,
IF(Peek(Forecast,ID,'Temp')<=[Stock on Hand],
1+(([Stock on Hand]-Peek(Forecast,ID,'Temp'))/Peek(Forecast,ID+1,'Temp')),
[Stock on Hand]/Peek(Forecast,ID,'Temp')) as [Days of Supply]
Resident Temp
Order By ID;
Drop Table Temp;
UPDATED : FINAL TABLE WITH ORDER BY ID
DONT FORGET TO SORT THE TABLE BY ID IN UI....
Hi sujeetsing,
thanks for the reply
Actually here i'm apply logic as below
if(Forecast>Peek(SOH),Peek(SOH)/Forecast,if(Forecast=Peek(SOH),1,if(Forecast<Peek(SOH),Peek(SOH)-Forecast=
1+if(Peek(SOH)-Forecast>Next day of Forecast,1,if(Peek(SOH)-Forecast = Next day of Forecast,1,
Peek(SOH)-Forecast/Next day of Forecast)))))
I am try to apply this logic but i'm not getting that how we implement the Next day of Forecast in the logic..
have you tried my solution?
if you do < instead of <=, there is no need of third condition....
IF(Peek(Forecast,ID,'Temp')<[Stock on Hand],
1+(([Stock on Hand]-Peek(Forecast,ID,'Temp'))/Peek(Forecast,ID+1,'Temp')),
[Stock on Hand]/Peek(Forecast,ID,'Temp')) as [Days of Supply]
Hi,
Try this
Test:
LOAD
date(date#(date, 'dd-mm-yy')) as date, Forecast, Production, Reciept, [Initial Stock], [Stock on Hand],
RowNo() AS RowNum
INLINE [
date, Forecast, Production, Reciept, Initial Stock, Stock on Hand
01-01-13, 1217, 795, 707, 600, 885
02-01-13, 1197, 670, 628, 885, 986
03-01-13, 1101, 585, 833, 986, 1303
04-01-13, 1161, 709, 550, 1303, 1401
05-01-13, 1431, 580, 529, 1401, 1079
06-01-13, 1226, 613, 737, 1079, 1203
07-01-13, 794, 717, 796, 1203, 1922
08-01-13, 969, 701, 654, 1922, 2308
09-01-13, 1277, 652, 641, 2308, 2324
10-01-13, 1045, 628, 850, 2324, 2757
11-01-13, 1100, 587, 767, 2757, 3011
12-01-13, 740, 692, 862, 3011, 3825];
FINAL:
Load *,
If([Stock on Hand] > Peek(Forecast,RowNum,'Test'), 1 + (([Stock on Hand] - Peek(Forecast,RowNum,'Test'))/ Peek(Forecast,RowNum+1,'Test')),
[Stock on Hand]/Peek(Forecast,RowNum,'Test'))as [Days of Supply]
Resident Test
Order by date;
Drop Table Test;
Regards,
Jagan.
Thank u so much Manish for the fast reply .
Its working by logically but in last when we see for stock on hand 2324 the days of supply is 2.16 but it should be 2.2418 days as below in the screen.@
Can you double check with your own calculation?
My one is looking OK...may be mistake at your end..
Thank u so much Jagan ..