Bottom 5 values

[Anonymous]

Hi,

I have two charts that shows (or are supposed to show) top 5 and bottom 5. The top 5 is no problem, here's my expression in the dimension:

=aggr(if(rank(sum({$<AccountName={'3010'}>} Value))

  <=5) ,Product),Product)

And for the bottom 5, i thought that this would work:

=aggr(if(rank(-sum({$<AccountName={'3010'}>} Value))

  <=5) ,Product),Product)

My problem seem to be the "-" sign in front of sum. Aggr doesn't seem to like it.

Any ideas of how i can achive this? Dimension limit is not an option here since the dimension i want to rank by is the secound dimension after a cyclic group.

//A.

[MK_QSL]

Check enclosed file..

[Anonymous]

if you right click on the chart/table and look at the expression for "Revenue%".

//A.

[MK_QSL]

There is no Revenue% in any file you have uploaded.

Looks like you have uploaded wrong file...

[Anonymous]

Great solution! thanks!

I'm trying to solve the Percentage rank now, should be doable.

[Anonymous]

ah, i saw that now. sorry! again .

Here the it is!

[MK_QSL · Accepted solution]

Change expressions as below

=sum({<Product = {"=Rank(-SUM({<Account = {'3010'}, Type = {'AC'}>}Value),4)<=5"}>}Value)/sum(TOTAL {<Product = {"=Rank(-SUM({<Account = {'3010'}, Type = {'AC'}>}Value),4)<=5"}>} Value)

or

=sum({<Product = {"=Rank(-SUM({<Account = {'3010'}, Type = {'AC'}>}Value),4)<=5"}>}Value)/sum(TOTAL Value)

[Anonymous]

Almost right.

I need to have account and Type in the fist set analysis aswell in order to get the right result.

In the testversion it works because the data is a bit poor but in the real version i nee to have it. so like this:

SUM({<Account = {'3010'}, Type = {'AC'}, Product= {"=Rank(SUM({<Account = {'3010'}, Type = {'AC'}>} Value),4)<=5"}>} Value)

Thanks again!

//A.