Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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?

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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?

View solution in original post

11 Replies
swuehl
MVP
MVP

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

Anonymous
Not applicable
Author

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?

Anonymous
Not applicable
Author

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?

Not applicable
Author

Hi there,

Please try this expression.

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

Thanks.

swuehl
MVP
MVP

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)?

Anonymous
Not applicable
Author

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?

swuehl
MVP
MVP

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?

Anonymous
Not applicable
Author

Stefan,

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

Thank you for your help

Anonymous
Not applicable
Author

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