topic Re: Days of Supply Problem in QlikView

Days of Supply Problem

Anonymous — Thu, 27 Feb 2014 06:52:49 GMT

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

Re: Days of Supply Problem

sujeetsingh — Thu, 27 Feb 2014 07:07:39 GMT

What wrong you are getting

Re: Days of Supply Problem

MK_QSL — Thu, 27 Feb 2014 07:29:28 GMT

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

Re: Days of Supply Problem

Anonymous — Thu, 27 Feb 2014 07:35:59 GMT

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

Re: Days of Supply Problem

MK_QSL — Thu, 27 Feb 2014 07:39:31 GMT

have you tried my solution?

Re: Days of Supply Problem

MK_QSL — Thu, 27 Feb 2014 07:45:42 GMT

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]

Re: Days of Supply Problem

jagan — Thu, 27 Feb 2014 07:53:34 GMT

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.

Re: Days of Supply Problem

Anonymous — Thu, 27 Feb 2014 08:03:24 GMT

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

Re: Days of Supply Problem

MK_QSL — Thu, 27 Feb 2014 08:15:00 GMT

Can you double check with your own calculation?

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

Re: Days of Supply Problem

Anonymous — Thu, 27 Feb 2014 08:18:54 GMT

Thank u so much Jagan ..

Re: Days of Supply Problem

jagan — Thu, 27 Feb 2014 08:28:28 GMT

Hi,

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

Regards,

Jagan.

Re: Days of Supply Problem

Anonymous — Thu, 27 Feb 2014 08:42:37 GMT

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

Re: Days of Supply Problem

MK_QSL — Thu, 27 Feb 2014 09:05:25 GMT

is your problem solved?

Re: Days of Supply Problem

Anonymous — Thu, 27 Feb 2014 09:36:20 GMT

Hi manish,

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

Please help me .

Re: Days of Supply Problem

MK_QSL — Thu, 27 Feb 2014 09:48:55 GMT

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.

Re: Re: Days of Supply Problem

Anonymous — Thu, 27 Feb 2014 14:51:50 GMT

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

Re: Days of Supply Problem

luizcdepaula — Thu, 20 Oct 2016 13:00:36 GMT

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,