4 Replies Latest reply: Mar 23, 2016 5:32 AM by Eric Hannert RSS

    Inventory days Sense

    Eric Hannert

      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

        • Re: Inventory days Sense
          Alex Timofeyev

          Eric,

           

          can you attach your app with some data?

           

          Alex

            • Re: Inventory days Sense
              Eric Hannert

              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

                • Re: Inventory days Sense
                  Alex Timofeyev

                  Hi Eric,

                   

                  and how should the end result look like?

                   

                  Alex

                    • Re: Inventory days Sense
                      Eric Hannert

                      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