Dynamic exclusion: What is possible and what not?

[andreas_koehler]

Hi !

I need to simulate cases against historical data in a flexible way.

For instant analysis it would be great to be able to convert a list box into an exclusion list box.  Seems that this is not possible.

So I sift through the community articles and found

Excluding List Box Members

and I adapted for my purposes in the attached example.

Quick info on the example:

By selecting an element in the ExcludeProduct list it is included in the variable vExclude ='=if(len(maxString(ExcludeProduct)),concat(distinct ExcludeProduct,','),'')

and the selection box Product is using the expression

if(  SubStringCount( '$(vExclude)' ,Product) = 0 ,Product)

My Questions are:

- vExclude variable: My understanding is that for allowing multiple selections I have to use an aggregation function like MaxString() after len(). But when I do so and I have selected none, I get a list of all elements of the ExcludeProduct list box.

Why?

Same happens when I use just

= concat(distinct ExcludeProduct,',')

Why?

- expression in list box 'Product':

I included the expression

=if(  SubStringCount( '$(vExclude)' ,Product) = 0 ,Product)

and tagged 'Hide excluded'

Why are the products that are excluded still visible?

- Expression in the simple table:

is there a better way than to use set analysis? Reason for asking is that when you many tables with many expressions and you need to exclude elements from another field (next time it might be 'category') you would have to go to each and every expression and change its set modifier. This is a hell lot of work.

Generally speaking: Dynamic exclusion like applied here seems like a rather complex endeavour. Is there a simpler way?

  Any help is highly appreciated.

Thanks,

Andreas

[sunny_talwar]

May be like this:

=If(GetSelectedCount(ExcludeProduct, FALSE(), 'Sim') = 0, Sum(SalesAmount), Sum({<Product = e({Sim}ExcludeProduct)>}SalesAmount))