7 Replies Latest reply: Jul 16, 2012 5:05 PM by washington alex RSS

    Period since the last delivery.

    washington alex

      Hello to all.

       

      I need a function that returns the number of days since the last entry of merchandise in stock.

      DATAEntrada
      01/01/20120,001,00
      02/01/20120,001,00
      03/01/20120,001,00
      04/01/20120,001,00
      05/01/20120,001,00
      06/01/20120,001,00
      07/01/20120,001,00
      08/01/20120,001,00
      09/01/20120,001,00
      10/01/20120,001,00
      11/01/20120,001,00
      12/01/20120,001,00
      13/01/20121,001,0013 Day
      14/01/20120,001,00
      15/01/20120,001,00
      16/01/20120,001,00
      17/01/20120,001,00
      18/01/20120,001,00
      19/01/20121,001,006 Day
      20/01/20120,001,00
      21/01/20120,001,00
      22/01/20120,001,00
      23/01/20121,001,004 Day

       

      thank you.

        • Re: Period since the last delivery.
          Wojciech Parzyszek

          in load script or in chart expression?

          • Re: Period since the last delivery.
            Steven Blower

            Hi

             

            Here is one solution.  This one calculates the values of 'days since merchandise was last in stock' using script, allowing you to load results as data values rather than calculating them on the fly in a chart.

             

            Based on your results table you only need to load the DATA and Entrada fields.  I renamed the DATA field as STOCKDATE for clarity and named the table 'Raw'.

             

            STEP 1:

            The first step it to ensure that your data is loaded in ascending order by the STOCKDATE.  Depending upon how you load your data you can either ensure the data is sorted before loading or you can use the ORDER BY function in the load script to sort the data as it loads. 

             

            STEP 2:

            The second step involves identifying the first date in your list of STOCKDATES and assigning this value to a variable:

             

            LET varMINDATE = Num(PEEK('STOCKDATE',0,'Raw'));

             

            STEP 3:

            The third step involves finding the dates on which merchandise was in stock.

             

            QUALIFY*;

            UNQUALIFY STOCKDATE;

            STOCKDATES:

            Load *

            Resident Raw

            WHERE Entrada>0

            Order By STOCKDATE ASC;

            UNQUALIFY *;

             

            This script uses a 'WHERE' clause to only load data for dates on which merchandise was in stock and then sorts that data in ascending order by STOCKDATE.  The table, which I called STOCKDATES, is qualified to avoid any conflict with the Raw table.

             

            STEP 4:

            The fourth step uses the PREVIOUS function to help calculate the number of days between the days on which merchandise was in stock.  It then uses a left join to attach that value to the relevant value of STOCKDATE in the Raw table.

             

            Left join (Raw)

            Load

            STOCKDATE,

            If(IsNull(Previous(STOCKDATE)),

            num(STOCKDATE)-num($(varMINDATE))+1,

            STOCKDATE-Previous(STOCKDATE)) as DAYS_SINCE_LAST

            Resident STOCKDATES;

             

            When there is no stock on the first date the Previous function will return a null value.  You can use this along with the variable varMINDATE to determine the number of days since the start of your period.  As there was no stock on the first day you have to add 1 to the calculated value to arrive at the first value of days without stock i.e. 13 days in your example.

             

            STEP 5:

            The final step just involves releasing the memory used by the STOCKDATES table by dropping it:

             

            Drop Table STOCKDATES;

             

            Complete Script:

            So the full script, including your table as an inline loaded table for this example is:

             

            SET ThousandSep=',';

            SET DecimalSep='.';

            SET MoneyThousandSep=',';

            SET MoneyDecimalSep='.';

            SET MoneyFormat='£#,##0.00;-£#,##0.00';

            SET TimeFormat='hh:mm:ss';

            SET DateFormat='DD/MM/YYYY';

            SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

            SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

            SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

             

             

            Raw:

            LOAD * INLINE [

                STOCKDATE, Entrada

                01/01/2012, 0

                02/01/2012, 0

                03/01/2012, 0

                04/01/2012, 0

                05/01/2012, 0

                06/01/2012, 0

                07/01/2012, 0

                08/01/2012, 0

                09/01/2012, 0

                10/01/2012, 0

                11/01/2012, 0

                12/01/2012, 0

                13/01/2012, 1

                14/01/2012, 0

                15/01/2012, 0

                16/01/2012, 0

                17/01/2012, 0

                18/01/2012, 0

                19/01/2012, 1

                20/01/2012, 0

                21/01/2012, 0

                22/01/2012, 0

                23/01/2012, 1

            ]

            ;

             

             

            LET varMINDATE = Num(PEEK('STOCKDATE',0,'Raw'));

             

             

            QUALIFY *;

            UNQUALIFY STOCKDATE;

            STOCKDATES:

            Load *

            Resident Raw

            WHERE Entrada>0

            Order By STOCKDATE ASC;

            UNQUALIFY *;

             

             

            Left join (Raw)

            Load

            STOCKDATE,

            If(IsNull(Previous(STOCKDATE)),

            num(STOCKDATE)-num($(varMINDATE))+1,

            num(STOCKDATE)-num(Previous(STOCKDATE))) as DAYS_SINCE_LAST

            Resident STOCKDATES;

             

              

            Drop Table STOCKDATES;

             

            To see the results simply create a TableBox with the dimensions STOCKDATE, Entrada, DAYS_SINCE_LAST

             

            E.G.

             

            STOCKDATEEntradaDAYS_SINCE_LAST
            01/01/20120
            02/01/20120
            03/01/20120
            04/01/20120
            05/01/20120
            06/01/20120
            07/01/20120
            08/01/20120
            09/01/20120
            10/01/20120
            11/01/20120
            12/01/20120
            13/01/2012113
            14/01/20120
            15/01/20120
            16/01/20120
            17/01/20120
            18/01/20120
            19/01/201216
            20/01/20120
            21/01/20120
            22/01/20120
            23/01/201214

             

             

            I hope that helps you to achieve your aim.

             

            Kind regards

             

            Steve