4 Replies Latest reply: Sep 30, 2017 6:35 AM by Antonio Mancini RSS

    How to get the amount change between different date

    Rex Liao

      Hi all,

      Assuming I have a table below:

       

      Date               Security_ID     Shares

      2017/9/29       BUMI              100

      2017/9/29       ELSA              30

      2017/9/28       BUMI              120

      2017/9/28       ELSA              20

       

      Is it anyway to get Sum[(Si(T) - Si(T-1))^2]?

      As an example of the table above, how to get  (100-120)^2 + (30-20)^2 = 500 ?

        • Re: How to get the amount change between different date
          Antonio Mancini

          Hi,

          may be this

          =Sum(Aggr(Pow(RangeSum(Above(Shares)-Shares),2),Security_ID,Date))

          Regards,

          Antonio

          • Re: How to get the amount change between different date
            Rex Liao

            Dear Antonio,

            It works well when there are only have data in past 2 days.

            If there are data in past 3 days, is it possible to get value between 2017/9/28 and 2017/9/29?

            As the example of the table below, the value should be 20,000.

             

            LOAD * Inline [

            Report_Date, Security_ID, Shares

            2017/9/29, BUMI, 100

            2017/9/29, ELSA, 100

            2017/9/29, MLPL, 100

            2017/9/29, PGAS, 100

            2017/9/29, PTPP, 100

             

            2017/9/28, BUMI, 100

            2017/9/28, ELSA, 100

            2017/9/28, MLPL, 100

            2017/9/28, PGAS, 200

            2017/9/28, PTPP, 0

             

            2017/9/27, BUMI, 50

            2017/9/27, ELSA, 50

            2017/9/27, MLPL, 50

            2017/9/27, PGAS, 50

            2017/9/27, PTPP, 50

            ];

              • Re: How to get the amount change between different date
                Antonio Mancini

                Temp:
                LOAD * Inline [
                Date, Security_ID, Shares
                2017/9/29, BUMI, 100
                2017/9/29, ELSA, 100
                2017/9/29, MLPL, 100
                2017/9/29, PGAS, 100
                2017/9/29, PTPP, 100
                2017/9/28, BUMI, 100
                2017/9/28, ELSA, 100
                2017/9/28, MLPL, 100
                2017/9/28, PGAS, 200
                2017/9/28, PTPP, 0
                2017/9/27, BUMI, 50
                2017/9/27, ELSA, 50
                2017/9/27, MLPL, 50
                2017/9/27, PGAS, 50
                2017/9/27, PTPP, 50]
                ;
                LOAD *,
                If(Security_ID=Previous(Security_ID),Pow(RangeSum(Previous(Shares)-Shares),2)) as Shares1
                Resident Temp
                Order By Security_ID,Date Asc;
                Drop Table
                Temp;