Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Days of Supply Problem

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...

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

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.

View solution in original post

16 Replies
sujeetsingh
Master III
Master III

What wrong you are getting

MK_QSL
MVP
MVP

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....

Anonymous
Not applicable
Author

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..

MK_QSL
MVP
MVP

have you tried my solution?

MK_QSL
MVP
MVP

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]

jagan
Luminary Alumni
Luminary Alumni

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.

Anonymous
Not applicable
Author

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.supplyImage.PNG.png@

MK_QSL
MVP
MVP

Can you double check with your own calculation?

My one is looking OK...may be mistake at your end..

Anonymous
Not applicable
Author

Thank u so much Jagan ..