Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All -
I need to select the top N values for a field when a user navigates to a tab. I'm experimenting with the Adventureworks DB and created a listbox with the correct values, but can't figure out how to translate it to a trigger. The expression I have so far is:
=If(Aggr(Rank(Sum(SalesAmount)),City) < 5, City)
and it works fine and gives me the top 4 Cities by sales. How do I translate that to the Search String for a Select in Field trigger (or action)?
Thanks in advance,
Mike
www.fortunecookiebi.com
Yup you are right. Should use concat.
I used this formula and it worked.
='('&concat(if(Aggr(Rank(sum(Sales)),City)<4,City),'|')&')'
Also attached a sample qvw with some dummy data. If you click the second tab 'Tab with Trigger' it will automatically select top three cities.
Does this meet your requirement?
Thanks
AJ
Hey Mike,
You can go to Settings -> Sheet Properties -> Triggers and click Add Actions under OnActivateSheet.
Here you can select 'Select in Field' and give City as the Field Name and following expression in search string:
=If(Aggr(Rank(Sum(SalesAmount)),City) < 5, '('& Replace(City,',','|')&')')
Please let me know if you find any issues.
Thanks
AJ
Hi AJ,
Thanks for the reply. Unfortunately, I can't get it to work. If I put
'('& Replace(City,',','|')&')'
into a text object and select a single City, it shows, for example (Paris). If I select multiple values from City, it shows ()
I've been messing with it and think it needs a 'concat' in there somewhere, but still can't get it right. I tried
'(' & Concat(Distinct City,'|') & ')'
and that brings back what looks like the right format for multiple values
but the trigger won't set the values.
I think we're close... Any thoughts?
Thanks!
Mike
www.fortunecookiebi.com
Yup you are right. Should use concat.
I used this formula and it worked.
='('&concat(if(Aggr(Rank(sum(Sales)),City)<4,City),'|')&')'
Also attached a sample qvw with some dummy data. If you click the second tab 'Tab with Trigger' it will automatically select top three cities.
Does this meet your requirement?
Thanks
AJ
Thanks, AJ!
That is awesome!