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!

[sunny_talwar]

Would you be able to share a sample where you are using this to help you better?

[dreweezy]

Hi Chana, I created a quick sample qvf as I cannot disclose some data provided in my original. 

On the attached what I need to do is using the variable button, I need to find the top 5 and bottom 5 for accounts 1 and 2 separably. I have an if statement to go against the accounts 1 and 2 but within the if statement I need to explicitly tell it that I need the top 5 for accounts 1 and top 5 for accounts 2.

if($(vAccount) = 1,  sum (Revenue({< Account = {"Account 1"}>} )),

if($(vAccount) = 1, sum (Revenue({< Account = {"Account 2"}>} )))

Im looking for something like this but not sure how to put these together in order for it to work. Qvf on the attached. Thanks again.

[sunny_talwar]

Is this what you are after?

---

@dreweezy wrote:

Hi Chana

---

BTW who is Chana? 🙂

[dreweezy]

Hahaha Sunny please excuse me. Names were mixed up there for a second. 

Yes this is what I need. But only for account 1. Once I bring in my variable in this syntax..

if($(vAccount) = 1, .........

it doesn't yield any results. I'm essentially using nested ifs in order to point it to account 1 (using my variable) and another if for the rank function.

[sunny_talwar]

Why don't you make a selection in Account Type = 'Account 1' to see just Account 1?

[dreweezy]

that would be the simplest way but as a requirement, it is expected to yield the top 5 and bot 5 on the push of the button.

if($(vAccount) = 1, Aggr(If(Rank(Sum(Revenue)) < 6, Sum(Revenue)), Account_Title, Account))

This is what I've been trying just to test with the 1st button (account 1). Where can I say in the expression to give me Aggr(If(Rank(Sum(Revenue)) < 6, Sum(Revenue)), Account_Title, Account)) where Account_Title = 'Account1'

Thank you.

[sunny_talwar]

May be this

If($(vAccount) = 1, Aggr(If(Rank(Sum({<Account_Title = {'Account1'}>} Revenue)) < 6, Sum({<Account_Title = {'Account1'}>} Revenue)), Account_Title, Account))

[dreweezy]

Thanks Sunny, I had something similar to this where I tell them account title = a specific value. Not getting any results but i know the answer is right there somewhere.

[sunny_talwar]

Seems to work for me 🙂