Set Analysis - include some field values and exclude others

[marinasbc]

Hi all,

I need to have a set analysis expression that excludes some field values like:

Type	Subtype
A	A1
A	A2
B	B1
B	B2
B	B3
B	B4

I have a table with Type A values (and a listbox only with subtypes A1 and A2) - let's call it TableA.

I have another table only showing Type B values - Table B. For this table, I have a listbox with Sybtype (built with aggr) that only shows B subtypes (B1, B2, B3, B4).

When I select subtypes A1 or A2, I don't want values on table B to change at all.

When I select subtypes B1, B2, B3 or B4 I want values on Table B to change accordingly.

Focusing on Table B:

I have tried expressions like Sum({< Type={B}, Subtype-={A1,A2} >} Amount) but that always selects all values from Subtype B. I have tried using P() and Sum({< Type={B}, Subtype*={"*"}-{A1,A2} >} Amount) but no success.

I need to ignore Type A selections but add to selection any Type B values.

Anyone know how can I do this?

Thanks!

[sunny_talwar]

May be something like this

=If(WildMatch(Concat(TOTAL DISTINCT '|' & Subtype & '|'), '|B*'), Avg(1), Avg({<Subtype += {"B*"}>} 1))

When A1 is selected

When B2 is selected

[marinasbc]

Hi Sunny,

Thanks so much!

But I have a lot of calculations (just sent in a very simple example), so it would be complicated to duplicate everything.

I decided to go with a data island for the subtypes and then create a variable that concatenates them to use in set analysis.

Like: Sum({< Subtype = {$(vSubtypesList)} >} Amount)

vSubtypesList = concat({<Type={'$1'}>}distinct chr(39)&%PickSubtypeField&chr(39))

Thanks again for your helpful answer.

[sunny_talwar]

Not sure I understand the new approach, but have you tried this

Sum({<Subtype = p(%PickSubtypeField)>} Amount)