Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Showing Excluded options in a Listbox with aggr(if..) expression

Hi,

I'm using the following expression in a listbox to denote countries that can be selected based on a given criteria e.g. if the sales of a country is > $100K , as set by the variable vCountryLimit.

=aggr(if( sum($(vCountryMetrics))>vCountryLimit, Country), Country)

This works well in a sense that it only shows up countries that meet the criteria and hides everything else.

However, it will also hide the Excluded options, which defeats the effectiveness of associative query using the power of Gray.

I know this can be done via ETL at the script level but I need to have different list boxes based on different criteria e.g. if a country > 100K in X region for one list box and >500K in Y region etc. So limiting this at the ETL layer is not an effective solution for me.

What can I do to limit the country list without using ETL, and yet still achieve the Gray excluded options in the display?

Thanks in advance!

1 Solution

Accepted Solutions
evan_kurowski
Specialist
Specialist

Hello William,

In order to prevent your expression list box from reducing to just selected values, the ignore set-analysis needs to be placed in every location where a field selection would reduce the underlying result set.

Maybe give this a try:

Aggr( If( Sum( {1} $(vCountryMetrics) ) > vCountryLimit  ,Only({1} Country),Null()),Country)

View solution in original post

4 Replies
Anonymous
Not applicable
Author

Hi,

Thanks for your response.

Unfortunately, that didn't work...

The display on the right is what I want to achieve, but I keep getting the one on the left, even if I used ur expression i.e. with the {1} set addition

screenshot.png

evan_kurowski
Specialist
Specialist

Hello William,

In order to prevent your expression list box from reducing to just selected values, the ignore set-analysis needs to be placed in every location where a field selection would reduce the underlying result set.

Maybe give this a try:

Aggr( If( Sum( {1} $(vCountryMetrics) ) > vCountryLimit  ,Only({1} Country),Null()),Country)

Anonymous
Not applicable
Author

@Evan,

Thanks! That did the trick!!

I used

=aggr(if( sum({1}$(vCountryMetrics))>vCountryLimit, Only({1}Country)), Country)

since my listbox only uses 1 dimension

evan_kurowski
Specialist
Specialist

Excellent William, glad it is working.

I think what you removed wasn't a second dimension, but the ELSE clause of the IF statement assigning 'Null()', which produces the same result.  Really like dynamic list boxes though!