qsVariable to render chart based on newly created buttons

[dreweezy]

Posted this initially on the wrong thread. 

Hi - wondering if anyone have any thoughts on the approach of my problem.

I want to have my bar chart render on a click of a button to show me 'Top 10' and 'Bottom 10' values based on my 'sales' and 'account' columns. 

I have my syntax ready and believe they should be placed in the actual button section:

Top 10 = 
sum({<Account={"=Rank(Sum(Sales))<=10"}>} Sales)

Bottom 10 = 
SUM({<Account = {"=Rank(-SUM(Sales)<=10"}>}Sales)

My confusion - on the below image, I am not sure how this linkage is created given that I can only tie this qsVariable extension to one variable (vTopBot).

I am also unsure what I am supposed to define within my given variable in order to produce the top and bot 10 values for my chart. Any help would be greatly appreciated

[Channa]

top 10 dimension

=If(Aggr(Rank(Sum(Sales)),Account)<=10,Account)

measure =Sum(Sales)

[dreweezy]

I have tried that syntax and it doesn't seem to be working. My variables has Top 10 (=1) and Bottom 10 (=2) and I am trying to write out an IF statement so that I am able to grab the top and bottom 10 on a click of the button. Here is a screen shot of what I have set up.

[Channa]

bro i amusing below expression to show top 10 countries in my dashboard

=If(Aggr(Rank(Sum(Sales)),Account)<=10,Account)

i just replace country with your account

for bottom 10 use below

=If(Aggr(Rank(-Sum(Sales)),Account)<=10,Account)

and then use IF condition 

if(variable=1,If(Aggr(Rank(-Sum(Sales)),Account)<=10,Account),If(Aggr(Rank(Sum(Sales)),Account)<=10,Account))

[dreweezy]

Hi Chana, thanks for your advice. I am still not getting any answer on my bar chart. I followed exactly how you have yours set up. Just for testing purposes I just did the first [portion for top 10.

=If($(vTopBot)=1, Aggr(Rank(Sum(SALES)),ACCOUNT)<=10,ACCOUNT)

Does that syntax seem right? Do I need single quotes around my variable?

[Channa · Accepted solution]

If(vTopBot=1,Sum({< ACCOUNT  ={"=Rank(Sum( SALES  ))<=10"}>} SALES  ),Sum({< ACCOUNT  ={"=Rank(-Sum( SALES  ))<=10"}>} SALES  ))

try this

[dreweezy]

Channa, thank you very  much. It is now going somewhere. My new problem is it seems to be showing more than 

I do have the '10' set up in the syntax so my understanding is that it should take in the rank for top 10 and bottom 10. I also excluded the null values in the dimension. Any thoughts?

[dreweezy]

Scratch that - I was able to get the top 10 and bottom 10. I had to set do the data handling to exclude null and 0's! Thank you very much. I did have one last question - say I wanted create one more button that will just show everything so my variables will be.. 

1 - top 10

2- bottom 10

3- all/everything

how would my syntax be set up?

If(vTopBot=1,Sum({< ACCOUNT  ={"=Rank(Sum( SALES  ))<=10"}>} SALES  ), if(vTopBot=2 Sum({< ACCOUNT  ={"=Rank(-Sum( SALES  ))<=10"}>} SALES  ) else) something like this?