Solved: Days of Supply Problem - Page 2 - Qlik Community

Anonymous · ‎2014-02-27

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

jagan · ‎2014-02-27

Hi,

When calculated manually the value is 2.162 not 2.2418 crosscheck again.

Regards,

Jagan.

Anonymous · ‎2014-02-27

Hi Jagan,

Please give me idea ,Actually i want if Stock on Hand after the comparison of Forecast is Remaining then we will consider only that days as below for Stock on Hand 2757 the days of supply should be 3 and for Stock on Hand 3011 the days of supply should be 2 .

the calculation of days of supply for Stock on Hand 2757 as { 2757-1100 = 1657 and then 1657 -740=917 and then 917-878 = 39} at this situation we consider only 3 days for days of supply as usually for Stock on Hand 3011.

Please help me .

thanks in advance

MK_QSL · ‎2014-02-27

is your problem solved?

Anonymous · ‎2014-02-27

Hi manish,

thanks a lot its working but i want this thing also as below

Please give me idea ,Actually i want if Stock on Hand after the comparison of Forecast is Remaining then we will consider only that days as below for Stock on Hand 2757 the days of supply should be 3 and for Stock on Hand 3011 the days of supply should be 2 .

the calculation of days of supply for Stock on Hand 2757 as { 2757-1100 = 1657 and then 1657 -740=917 and then 917-878 = 39} at this situation we consider only 3 days for days of supply as usually for Stock on Hand 3011.

Please help me .

MK_QSL · ‎2014-02-27

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.

Anonymous · ‎2014-02-27

Hi Manish,

here ur logic is correct its working but when we apply that logic in my whole data then its not working properly from starting its work but in middle its give the wrong 'days of supply' as below in the image I have marked this is the wrong 'days of supply' and the below of marking the all data gives the wrong value. Please if possible then check it and help me . thank u so much .

I also attach my qvw and data also ..

luizcdepaula · ‎2016-10-20

Hello Dhiraj,

Have you ever developed the right statement for this logic?

I need exactly what you specified before. A DOS logic that look forward and and if the forecast is higher than the demand, it keeps looking at every day in the future to figure out the amount of DOS.

Thanks,

LD