Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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?
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
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?
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?
Hi there,
Please try this expression.
sum(if((rank(sum([Ac.valuation]))<=20, [Ac.valuation])
Thanks.
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)?
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?
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?
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
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