DOS related Problem

[Anonymous]

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

[marcus_sommer]

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

[alexandros17]

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

[Anonymous]

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

[marcus_sommer]

Take only -1 to refer to the last record.

- Marcus

[Anonymous]

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

[marcus_sommer]

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

[Anonymous]

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