Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
mikecrengland
Creator III
Creator III

Help with Select in Field Trigger

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

1 Solution

Accepted Solutions
Not applicable

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

View solution in original post

4 Replies
Not applicable

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

mikecrengland
Creator III
Creator III
Author

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 ()


1-7-2014 8-55-18 AM.png

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


1-7-2014 8-58-35 AM.png


but the trigger won't set the values.


I think we're close... Any thoughts?


Thanks!


Mike

www.fortunecookiebi.com


Not applicable

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

mikecrengland
Creator III
Creator III
Author

Thanks, AJ!

That is awesome!