topic Re: Select Excluded, even when a value is selected in QlikView

Select Excluded, even when a value is selected

crystles — Wed, 14 Dec 2016 18:41:27 GMT

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.

Re: Select Excluded, even when a value is selected

vinieme12 — Wed, 14 Dec 2016 18:48:26 GMT

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.

Re: Select Excluded, even when a value is selected

vinieme12 — Wed, 14 Dec 2016 18:49:24 GMT

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

Re: Select Excluded, even when a value is selected

crystles — Wed, 14 Dec 2016 18:56:42 GMT

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

Re: Select Excluded, even when a value is selected

vinieme12 — Wed, 14 Dec 2016 19:05:43 GMT

Create a variable to capture the Customer Selections

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

and use below expression

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

Re: Select Excluded, even when a value is selected

vinieme12 — Wed, 14 Dec 2016 19:07:40 GMT

or even this should work

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

Re: Select Excluded, even when a value is selected

crystles — Wed, 14 Dec 2016 19:18:45 GMT

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

Re: Select Excluded, even when a value is selected

crystles — Wed, 14 Dec 2016 19:24:10 GMT

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.

Re: Select Excluded, even when a value is selected

vinieme12 — Wed, 14 Dec 2016 19:26:53 GMT

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?

Re: Select Excluded, even when a value is selected

vinieme12 — Wed, 14 Dec 2016 19:36:54 GMT

Check the attached

Re: Select Excluded, even when a value is selected

crystles — Wed, 14 Dec 2016 19:42:16 GMT

I am so sorry, I feel very dumb. This does work.I have the state selected but I didn't have a dealer selected.

It works when you select a dealer. I used this formula before and just assumed it wasn't working. But I didn't have the dealer selected so of course it wouldn't work.

Thank you so much for your help! This worked perfectly.

I should have had more faith in myself to begin with