Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
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;
Hi,
may be this
=Sum(Aggr(Pow(RangeSum(Above(Shares)-Shares),2),Security_ID,Date))
Regards,
Antonio
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;
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
];
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;