Need to count the # of IDs that have 2 different instances in 1 field

[#1]

I have a requirement where there is a bunch of marketing data that identifies the method of delivery.

They want a dynamic view for users that allow them to select 2 or more types and show them all the individuals that have had marketing delivered via that method.

Unfortunately I can't really change my data model.

I've included a simple qlikview doc that shows what I'm trying to do.

Basically if I can setup a textbox which shows the # of IDs that have both Event & Email. I've tried using a combined set analysis but it returns 0 since no single record can have both values in one field.

Not sure how to tackle this.

Thanks,

Rob

[maxgro · Accepted solution]

try

=Count( DISTINCT

  {$ <ID=P({$ <[Delivery Method]={Email}>} ID)>*<ID=P({$ <[Delivery Method]={Web}>} ID)>}

  ID)

[#3]

Worked like a charm. Thank you.

Is there some documentation about this P function? Not sure how it is doing this.

[maxgro]

1)

Set Analysis: syntaxes, examples

2)

the Qlik help

here

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/ChartFunctions/SetAnalysis/set-m...

or this copied from the help (F1)

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.