Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have data in a table :
Client_ID | IN/OUT | ISIN | date | Price | QTY |
1 | I | 6 | 19-Sep-12 | 10 | 300 |
1 | I | 7 | 19-Sep-12 | 11 | 250 |
1 | O | 7 | 19-Sep-12 | 11 | 100 |
1 | O | 6 | 19-Sep-12 | 10 | 250 |
2 | I | 7 | 19-Sep-12 | 11 | 100 |
2 | O | 7 | 19-Sep-12 | 11 | 50 |
2 | I | 8 | 19-Sep-12 | 9 | 500 |
I have an issue where im trying to use the aggr function. I would like data output to be in the following form:
Client ID | Value |
1 | 2150 |
2 | 5050 |
Value =
aggr( sum( if( sum(
if (IN/OUT='O' , -QTY, QTY)
)>'0' ,
Price * sum(if(IN/OUT='O',- QTY, QTY ) )
) )
,ISIN)
So for ClIENT_ID=1 the value would be 150 * 1+ 50 *10 =2150
But somehow the above function isnt working in a straight table.
Regards
Faisal
Maybe like this?
=sum(
aggr(
if( sum( if ([IN/OUT]='O' , -QTY, QTY) )>'0' ,
Price * sum(if([IN/OUT]='O',- QTY, QTY ) ) )
, Client_ID,ISIN)
)
Maybe like this?
=sum(
aggr(
if( sum( if ([IN/OUT]='O' , -QTY, QTY) )>'0' ,
Price * sum(if([IN/OUT]='O',- QTY, QTY ) ) )
, Client_ID,ISIN)
)
What about this?
Sum(DISTINCT Aggr(sum( if("IN/OUT"='O',- QTY, QTY ) ) *Price,Client_ID,ISIN ) )
With Client_ID as dimension