using rank function and variable simultaneously

[dreweezy]

Hi,

I need help with a syntax check. I have a table that has a column and measure. I've always got 4 buttons created using a variable to show me all the revenue for a given company. I'd ideally like to have my table generate and show me the top 10 revenue for a given company. 

Current syntax:

if($(vTop10) = 1, (sum({< Company = {"K-Mart"} >} [Items Revenue])),
if($(vTop10) = 2, sum({< Company = {"Wal-Mart"}>} [Items Revenue]),
if($(vTop10) = 3, sum({< Company = {"Best-Buy"}>} [Items Revenue]),
if($(vTop10) = 4, sum({< Company = {"Target"}>} [Items Revenue])))))

This works great. I am able to select any of the 4 buttons and it will give me the greatest sale goin from highest to low. I'd like to add a rank function so that now the table will only max have 10 highest values. I've tried the fixed limitation on the dimension but no luck since I'm working with a table. 

If(Rank([Items Revenue], 4) < 11, [Items Revenue])

This rank syntax works fine alone, but I am unsure on how to incorporate these 2 together with my given variable. Any help would be appreciated!

[dreweezy]

Thank you very much, I guess my previous comment never went through! 

[dreweezy]

Hi Sunny,

I actually had one more question regarding my problem. I was finally able to get the top 5 on a table using the aggr, rank, and also incorporating my variable which holds the value to pass.

if($(vTopBottom) = 1, Aggr(If(Rank(Sum({<AccountType = {'Account1'}>} [Revenue])) < 6, Sum({<AccountType = {'Account1'}>} [Revenue])), [AccountDescription]),

if($(vTopBottom) = 2, Aggr(If(Rank(Sum({<AccountType = {'Account2'}>} [Revenue])) < 6, Sum({<AccountType = {'Account2'}>} [Revenue])), [AccountDescription]),

if($(vTopBottom) = 3, Aggr(If(Rank(Sum({<AccountType = {'Account3'}>} [Revenue])) < 6, Sum({<AccountType = {'Account3'}>} [Revenue])), [AccountDescription]),

if($(vTopBottom) = 4, Aggr(If(Rank(Sum({<AccountType = {'Account4'}>} [Revenue])) < 6, Sum({<AccountType = {'Account4'}>} [Revenue])), [AccountDescription])))))

So this syntax returns the top 5 rows. Now for each account type there are negative revenue amounts. But when I make a change (i.e. put a "-" in front of sum function in order to get the negative values the expression does not properly work. I have 2 tables - one for top 5, and one for bottom 5. Is there any issue as to why putting a "-" wouldn't return me the negative values? I've also tried sorting my ascending order in the sort/filter section but still no luck there either.

[sunny_talwar]

Where exactly are you putting the negative sign? Would you be able to share a sample to show the issue?

[dreweezy]

I have attached my qvf and excel file in case. The top 2 tables are all the rows for a given country, product, and sales. The left table has the sum of sales desc top to bottom whereas the left table the values are ascending bottom to top. They are both working with the top 3 buttons using a variable I created.

My goal is create 2 tables based on these tables to grab the first 5 rows. One for top 5 and one for bot 5. I think I got the top 5 to work, but as you can see, the bot 5 shouldn't actually have negative values where I believe I am going wrong with putting the "-" sign in the wrong place. Also, on the top 5 chart, is there a way to just show exactly 5 rows and not additional rows that have no value in it?

Thanks again for your help.

[sunny_talwar]

Is this what you want?

[dreweezy]

Thanks Sunny, I think what I am looking for is this picture below.

I hope this makes sense. For some reason it looks like it is aggregating the data. I'd like to just grab the first 5 rows on the top 2 tables. 

[sunny_talwar]

This?

Expression

Aggr(
if($(vTopBot) = 1,
If(Rank(TOTAL Sum({<Segment = {'Enterprise'}>} Sales)) < 6, Sum({<Segment = {'Enterprise'}>} Sales)),

if($(vTopBot) = 2,
If(Rank(TOTAL Sum({<Segment = {'Government'}>} Sales)) < 6, Sum({<Segment = {'Government'}>} Sales)),

if($(vTopBot) = 3,
If(Rank(TOTAL Sum({<Segment = {'Midmarket'}>} Sales)) < 6, Sum({<Segment = {'Midmarket'}>} Sales))

)
)
)
, Country, Product)

[dreweezy]

Sunny, thank you this is exactly what I was looking for. I guess the TOTAL function here? Also, I needed to create another one for the bottom 5 rows, I tried putting a "-" in front of sum but it is not behaving as I expect. Is there a work around? The png below is what I am trying to achieve for the bottom 5 records.

I've tried sorting ascending here as well but it just sorts it with the values that I already have. You've been awesome. Thanks so much for all your help, really.

[sunny_talwar]

So, for the bottom 5... you are saying that the data is correct but the sorting is not correct?

[sunny_talwar · Accepted solution]

Is this what you are after?