Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
If(vTopBot=1,Sum({< ACCOUNT ={"=Rank(Sum( SALES ))<=10"}>} SALES ),Sum({< ACCOUNT ={"=Rank(-Sum( SALES ))<=10"}>} SALES ))
try this
top 10 dimension
=If(Aggr(Rank(Sum(Sales)),Account)<=10,Account)
measure =Sum(Sales)
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.
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))
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?
If(vTopBot=1,Sum({< ACCOUNT ={"=Rank(Sum( SALES ))<=10"}>} SALES ),Sum({< ACCOUNT ={"=Rank(-Sum( SALES ))<=10"}>} SALES ))
try this
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?
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?