AGGR function

[#1]

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

[swuehl · Accepted solution]

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)

)

[#3]

What about this?

Sum(DISTINCT Aggr(sum( if("IN/OUT"='O',- QTY, QTY ) ) *Price,Client_ID,ISIN ) )

With Client_ID as dimension