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

[Anonymous]

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!

[Anonymous]

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

[evan_kurowski · Accepted solution]

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]

@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]

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!