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]

I think you are adding the expression to the wrong tab on the list box properties. You need to add it to on the General tab, instead of Expressions Tab:

[sunny_talwar]

A simpler option is to use an expression like this:

=Aggr(Only({1<Product = e(ExcludeProduct)>}Product), Product)

and then you can avoid the whole need for saving things to a variable.

Check out the new qvw

[sunny_talwar]

Read about p() and e() functions here:

P() and E() in set analysis expression

[andreas_koehler]

Thanks Sunny,

Is there a way to resolve the problem that there are no products listed in the list  box when none is excluded?

And why? My vage understanding is because of 'Only' which is also the default. But why? if nothing is excluded the one and only one possible result from the aggregated data is = all elements of Products.

[sunny_talwar · Accepted solution]

Try now

[andreas_koehler]

Hah,

that looks familiar . I tried an if-clause myself to solve it but I chose the wrong pieces.

I saw just now that you use Aggr() without a measure. I had the wrong impression from one of HICs blogs and a book that Aggr() always requires a measure or otherwise takes Only().

Pitfalls of the Aggr function

But here Aggr(Product, Product) generated the list of Products.

Interesting.

Thanks Sunny, I learnt a lot today.

[sunny_talwar]

Well it depends on where you are using it. In a dimension and list box expression, you cannot use an outer aggregation function. I don't even think you will see a result if you add an outer aggregation. But as an expression, you do need an outer aggregation, or else it will assume to have only (which has the potential to become null if there are more than one values to display)

[andreas_koehler]

Sunny,

one last question to this solution.

I created 2 alternative sets to compare selections side-by-side. One [Sim] has the list of excluded values. But to evalute it properly I wanted to expressions in the graph, one for the original data and one for the simulation.

To keep things tidy I would like to have the "excluded" box in the alternative state [Sim] but then I have to define this in the expression. But how?

This expression creates an error message:

=If(GetSelectedCount({[Sim] * $} ExcludeProduct) = 0, Sum({[Sim] * $} SalesAmount), Sum({[Sim] * $<Product = e({[Sim] *$}ExcludeProduct)>}SalesAmount))

The workaround of keeing the "Excluded" box in the default state works, but I figure that this is not a good solution.

Andreas

[sunny_talwar]

So ExcludedProduct will be in alternate state and everything else in default or inherited state?