AGGR function

Wed, 19 Sep 2012 08:15:40 GMT

I have data in a table :

I have an issue where im trying to use the aggr function. I would like data output to be in the following form:

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

swuehl — Wed, 19 Sep 2012 09:20:26 GMT

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)

)

Wed, 19 Sep 2012 10:44:23 GMT

What about this?

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

With Client_ID as dimension