topic Re: Re: DOS related Problem in QlikView

DOS related Problem

Anonymous — Thu, 27 Feb 2014 10:57:20 GMT

Hi Everyone,

Here I have a script by using this script we are calculating DOS . in starting its working fine but in last it gives the wrong data ,my script as below-

Test:

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([Stock on Hand] > Peek(Forecast,ID,'Test'), 1 + (([Stock on Hand] - Peek(Forecast,ID,'Test'))/ Peek(Forecast,ID+1,'Test')),

[Stock on Hand]/Peek(Forecast,ID,'Test'))as [Days of Supply]

Resident Test

Order by ID;

Drop Table Test;

By using above script we r getting the following value initially its working but in last as marked in image its not give the correct value

i want according to 2757 stock on hand DOS should be only 3 and according to 3011 stock on hand DOS should be 2.

Please anyone help me to solve this problem .

Thanks

Re: DOS related Problem

marcus_sommer — Thu, 27 Feb 2014 11:26:39 GMT

Why do you use ID as counter for peek()? Peek() starts by 0 and -1 returned the last record - please have a look in Help.

- Marcus

Re: DOS related Problem

alexandros17 — Thu, 27 Feb 2014 11:29:18 GMT

When you refer to id with peek it is zero based so where you have written ID you must write ID-1 and ID+1 become ID

Hope it helps

Re: DOS related Problem

Anonymous — Thu, 27 Feb 2014 12:17:27 GMT

Hi Alessandro,

Thanks for the reply, but here when we r applying ID-1 in place of ID and ID in place of ID+1 its gives the wrong data..

Thanks

Re: DOS related Problem

marcus_sommer — Thu, 27 Feb 2014 12:34:12 GMT

Take only -1 to refer to the last record.

- Marcus

Re: DOS related Problem

Anonymous — Thu, 27 Feb 2014 12:44:00 GMT

Hi Marcus,

Thanks for the reply . Actually I have tried many times but not getting correct solution.

Please can u provide me a test demo of my data..

Thanks

Re: Re: DOS related Problem

marcus_sommer — Thu, 27 Feb 2014 13:08:15 GMT

I'm not sure if I have understand your business-logic and which result on which record would be the correct one. If you load all fields residental from a previous table you didn't need to refer on these table you could use the records within the own table.

Often it is helpful to load the peek()-results as a separate field - so you could better compare it they return the expected data - see the attachment.

- Marcus

Re: DOS related Problem

Anonymous — Thu, 27 Feb 2014 14:06:01 GMT

Hi Marcus,

Thank u so much for the reply. but that is not my actual requirement. As above below link the actual requirement.

Days of Supply Problem

Thanks