How to get the amount change between different date

[Anonymous]

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 ?

[antoniotiman]

Hi,

may be this

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

Regards,

Antonio

[antoniotiman]

or in Script

Temp:
LOAD * Inline [
Date, Security_ID, Shares
2017/9/29, BUMI, 100
2017/9/29, ELSA, 30
2017/9/28, BUMI, 120
2017/9/28, ELSA, 20];
LOAD *,
If(Security_ID=Previous(Security_ID),Pow(RangeSum(Previous(Shares)-Shares),2)) as Shares1
Resident Temp
Order By Security_ID,Date Desc;
Drop Table Temp;

[Anonymous]

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

];

[antoniotiman · Accepted solution]

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;