How to get the sum of top n values from expression?

[#1]

I have a bar chart with count(ID) as expression and yearMonth as dimension. I would like to get the sum of top n (for example top 10) numbers (by yearmonth) in that expression. Is this possible? Thanks

[MayilVahanan]

HI

Use rank function in expression or dimension limit in dimension tab..

Hope that helps

[#3]

Thank you for your helps. I have no idea of how to use Rank function to get sum of top n values of expression! Could you give me some detailed instruction?

[MayilVahanan]

Hi

Try like this

=if(aggr(rank(MonthName)<=10,MonthName), MonthName) in dimension

or

MonthName as dimension

Use if(rank(Count(ID)) <= 10, count(ID))

Edit:

For more information about rank function, please refer the manual or help..

Hope that helps

[#5]

Still cannot get it to work. I get sum of all the count values by using if(rank(Count(ID)) <= 10, count(ID)). Although we have a condition of rank(Count(ID)) <= 10, but I think Count(ID) still return sum of all the count value. What I need is the sum of top n-1 values. Please refer to my sample file. Thanks

[MayilVahanan · Accepted solution]

HI

Please see the attached file. Hope you can see the difference

[#7]

Big thanks. It works fine. Is there any way I can get the sum of FIRST (not TOP) n-1 count values? I realized that I had not asked correct question when I saw your solution. I do apologized for this.

[MayilVahanan]

HI

By using dimension limit option, you can set first n values.

[#9]

I can show the first n counted values. What I really need is to know is how to get SUM of the first n and last n counted values. If I can store this two sums in two different variables, that will be ideal. Still need your advice. I tried FIRST function, no success so far. Thanks.

[MayilVahanan]

HI

Try like this

= Count(YearMonth={"=Rank(Count(ID))<=10)"}>}ID)

Hope that helps