Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

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
Highlighted
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
Highlighted
Contributor III
Contributor III

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

Highlighted
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

Highlighted
Contributor III
Contributor III

@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

Highlighted
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!