2 Replies Latest reply: Sep 19, 2012 6:44 AM by Pedro Burgo

# AGGR function

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

• ###### Re: AGGR function

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)

)

• ###### Re: AGGR function

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