Inventory days Sense

[#1]

Hi community,

I need some help. My problem:

I have articlenumber, amount, date,delivery/sold as a table.

So I know how to get the date differents thats not the problem here.

My problem is that when I use a normal time diff function i only get it from sold to latest delivery.

But there can be 2 deliveries before a single sold.So I want to get the right delivery date for each sold.

I know I need to find the amount of the earliest delivery and count it down for every sold there is.

(delivery amount - sold amount - sold amunt ....)

But when the amount from delivery 1 is 0 i  need to switch to the second earliest by min(date,2).

But I definetly dont know how to do this in code in load script.

Hope someone here can help me!!!

thanks in advance!

-Eric

[alextimofeyev]

Eric,

can you attach your app with some data?

Alex

[#3]

Hi Alex,

the app is in a different language but i will edit this comment as fast as i can to provide detailed information

thx in advance

-Eric

-----------------------------------------------------------------

loadscript

LIB CONNECT TO 'DB';

InitialLoad:

LOAD articlenumber,

   amount as amount_VD,

   "sold/delivery",

    Date as Date_VD;

SQL SELECT

     articlenumber,

     amount as amount_VD,

    "sold/delivery",

     Date as Date_VD

FROM db.dbo.VD_TAB;  

---------------------------------------------------------------

examples

Articelnumber     amount     sold/delivery     date

0100179020       1              sold                 2016-01-07
0100180021       14            delivery              2016-01-25 0100180021       1              sold                   2016-01-07 0100180021       1             sold                  2016-01-25 0100180021       1             sold             2016-01-30

and hope u can understand my probleme with the delivery / sold dates and the negativ diff when i dont find the right delivery date.

Also there can be a delivery at 2016-01-24 with 35 units and then the difference must be calc with 24 until it 0 and then us 25 as delivery date.

hope u can help

[alextimofeyev]

Hi Eric,

and how should the end result look like?

Alex

[#5]

Hi Alex,

thx that ur interrested in helping me out.

so

0100179020 7.1 no delivery in timerange so no calc

0100180021 7.1. no delivery so no calc

0100180021 25.1 delivery at 25.1 so 0 day

0100180021 30.1 delivery at 25.1 so 5 day

0+5=5=> 5/2

avg 2,5 days

-------------------------------------------------

if there would be a delivery at 24.1 for 1 of 0100180021

there would be

0100180021 25.1 delivery at 24.1 so 1 day

=> delivery amoutn of 24.1 is no 1-1 so 0

that means 25.1 is no the delivery to look at

0100180021 301.1 delivery at 25.1 so 5 days

thats means

5+1=>6

6/2=3 days avg

hope u can help

-Eric