Select Excluded, even when a value is selected

[crystles]

I need to be able to have a button or chart that displays all the Excluded values of a selection, EVEN when one of them is selected.

I have a map with States. Customers are associated to each state, with some states having more than one Customer.

When they have a state and customer selected, I still want to be able to display the other Customers that are associated with the state.

So say California has 3 customers.

Customer 1

Customer 2

Customer 3

When Customer 2 is selected, I want to display the other two Customers (1 & 3) so the user can then select the other customers. If they want to switch between the other customers associated with the state, they can then select one of the other Customers specific to that state. Say they select Customer 1, then only 2 & 3 would be showing, etc.

Any help would be greatly appreciated.

[vinieme12]

From the Help file:

Set Modifiers with implicit field value definitions

In the above examples, all field values have been explicitly defined or defined through searches. There is however an additional way to define a set of field values by the use of a nested set definition.

In such cases, the element functions P() and E() must be used, representing the element set of possible values and the excluded values of a field, respectively. Inside the brackets, it is possible to specify one set expression and one field, e.g. P({1} Customer). These functions cannot be used in other expressions:

Examples:

sum( {$<Customer = P({1<Product={‘Shoe’}>} Customer)>} Sales )
returns the sales for current selection, but only those customers that ever have bought the product ‘Shoe’. The element function P( ) here returns a list of possible customers; those that are implied by the selection ‘Shoe’ in the field Product.

sum( {$<Customer = P({1<Product={‘Shoe’}>})>} Sales )
same as above. If the field in the element function is omitted, the function will return the possible values of the field specified in the outer assignment.

sum( {$<Customer = P({1<Product={‘Shoe’}>} Supplier)>} Sales )
returns the sales for current selection, but only those customers that ever have supplied the product ‘Shoe’. The element function P( ) here returns a list of possible suppliers; those that are implied by the selection ‘Shoe’ in the field Product. The list of suppliers is then used as a selection in the field Customer.

sum( {$<Customer = E({1<Product={‘Shoe’}>})>} Sales )
returns the sales for current selection, but only those customers that never bought the product ‘Shoe’. The element function E( ) here returns the list of excluded customers; those that are excluded by the selection ‘Shoe’ in the field Product.

[vinieme12]

what you need is  E(), but still this is a good read

sum( {$<Customer = E({1<Product={‘Shoe’}>})>} Sales )
returns the sales for current selection, but only those customers that never bought the product ‘Shoe’. The element function E( ) here returns the list of excluded customers; those that are excluded by the selection ‘Shoe’ in the field Product.

[crystles]

Yes, I have seen this post about e() and p() but I don't know how to make it work for my formula.

I tried this formula but the table just shows up blank.

Count({<STATE_REGISTRATION=e(DealerID)>} DealerID)

I have used e() with date formulas to get past years and such, but I can't think of how to make it work in this instance for some reason

[vinieme12]

Create a variable to capture the Customer Selections

vSelections = =Replace(GetFieldSelections(DealerID),', ','","')

and use below expression

count( {1<DealerID-={"$(=vSelections)"}>}  DealerID)

[vinieme12 · Accepted solution]

or even this should work

Count( {<DealerID= e(DealerID)>}  DealerID)

[crystles]

I tried the formula you supplied, but it just gave me a list of all of the Dealers with a count of each DealerID?

[crystles]

I'm sorry, this one didn't work either. I believe I tried this one to begin with, I just assumed I needed a more complex formula? But I'm not sure.

[vinieme12]

How are you trying it?

what are your dimensions? do you have any other expression in the same table?

I'm sure this works! Can you share a sample app / data?

[vinieme12]

Check the attached