11 Replies Latest reply: Nov 23, 2011 7:15 AM by Asta Zebelyte

# how to Sum Top clients by assets

I sum different assets for clients in Dimension using Sum function. I only need to present top 20, and I need grand total of that 20, but not total of al clients.

Tried using  Sum of rows. does not work.

Any ideas?

• ###### how to Sum Top clients by assets

You could use the rank function to get the top 20, like

if(rank(sum(Sales)) <=20, sum(Sales)

The total should be ok then, also.

Hope this helps,

Stefan

• ###### how to Sum Top clients by assets

Swuehl,

thank you for this. but still not doing what i need.

my function is:

if(rank(sum([Ac.valuation]))<=20, sum ([Ac.valuation]))

and i do descending rot by expression and add same expression as above. I get expression numbers all other the place not sorted at all and no total at the end.

any other suggestions?

• ###### how to Sum Top clients by assets

Swuehl,

thank you for this. but still not doing what i need.

my function is:

=if(rank(sum([Ac.valuation]))<=20, sum ([Ac.valuation]))

and i do descending rot by expression and add same expression as above. I get expression numbers all other the place not sorted at all and no total at the end.

any other suggestions?

• ###### how to Sum Top clients by assets

Ah ok, for the total you might need advanced aggregation, like

=sum( aggr( if(rank(sum([Ac.valuation]))<=20, sum ([Ac.valuation])) , ClientDimensionField))

I am not sure why you get not the expected results, could you maybe post a small sample app here (upload available in advanced editor)?

• ###### how to Sum Top clients by assets

Stefan,

the last advanced aggregation has to be in Expression or dimension?

can it be that this does not work either because i have too many dimensions with if statements?

• ###### how to Sum Top clients by assets

Use it as an expression - at least if I understand your situation and requirements right.

Hm, you are using a lot of calculated dimensions? This could be a problem with the aggr() function since this won't take calculated dimensions. Could you detail your used dimensions and expression a bit more? Or upload a small sample?

• ###### how to Sum Top clients by assets

Stefan,

as predicted, this function didint work because of if statements in Dimensions. By removing them from Dimensions, function works as expected.

• ###### how to Sum Top clients by assets

Stefan,

where would i specify only to sum certain account types not all available and still get top clients by assets.

As I cant put anything in dimesnions i need to specify in expressions

i need to sum up assets by [Account.Type]='Segregated'

Thanks

• ###### how to Sum Top clients by assets

You can use a set expression in your sum, something like

=sum( aggr(

if(rank(sum({<[Account.Type]={'Segregated'}>} [Ac.valuation]))<=20,

sum ({<[Account.Type]={'Segregated'}>} [Ac.valuation])

) , ClientDimensionField))

• ###### how to Sum Top clients by assets

Stefan,

max points from me on all yours answers!!!

Thank you so much.

Asta

• ###### Re: how to Sum Top clients by assets

Hi there,