Qlik Sense Set Analysis with the P() and E() functions

Every Friday at Bitmetric we’re posting a new Qlik certification practice question to our LinkedIn company page. Last Friday we asked the following Qlik Data Architect certification practice question about implicit field value definitions in Qlik Sense.

The answers were more or less evenly distributed between B and C.

The correct answer is C

The correct answer uses the P() function. This, along with its antonym E() function, is an element function. We’ll look at both in more detail in a minute, but first let’s quickly revisit the basic elements that are used to create a set analysis expression.

Dissecting the Qlik Sense Set Analysis expression

The diagram below shows a set analysis expression in which each of the individual components are labelled:

• A set expression is always enclosed in curly brackets: { }

• Optionally, a set identifier defines the base selection for the set expression

• Next, the modifications are specified in the set modifier, which is enclosed between angled brackets: < >

• Within the set modifier, we specify a field name for which we want to change the selection

• The type of change we want to make to the field name is specified by the assignment operator. Overwriting the initial selection with the = sign is the most common use, but we can also add, remove, intersect or xor the initial selection

• After the assignment operator, we specify the element list. This contains the value(s) that we want to assign to the field. The element list is enclosed in curly brackets: { }

Typically, in the element list we specify explicit values. Either by entering literal values (such as 100 or Value in the diagram above) or by retrieving values with Dollar-sign expansion. With the P() and E() functions however, we can retrieve implicit values. Let’s see how this works.

Implicit field value definitions in Qlik Sense Set Analysis

The element functions P() and E() select either the possible or the excluded values. They can be used in place of a regular element list. In its most basic form we can use the element function without any further decoration:

Sum({$<Customer=p(Customer)>} [Sales amount])

The expression above says: “Select the sum of sales for all customers that are included in the current selection”.

In itself, this is not a useful selection because by default the selection will already include all customers that are within the scope of the selection. Doing the opposite, with the E() function, is already more useful though:

Sum({$<Customer=e(Customer)>} [Sales amount])

This expression says: “Select the sum of sales for all customers that are excluded from the current selection”.

Now we can easily see how much revenue is generated by customers that fall outside the scope of our selection.

Adding nested Set Analysis

Where things get even more useful is when we add additional Set Analysis within the element function. This lets us define the scope for the selection. For example:

Customer=p( {1<SalesYear={2023}>} Customer)

Now we’re selecting all customers, in the full data set (1), that we’ve made a sale to in the year 2023 (SalesYear={2023}).

Or consider the following example:

Customer=e( {$<Product={'Bagel'}>} Customer)

Here we select all customers within the current selection ($) that haven’t bought a Bagel. You can imagine that you can make some pretty interesting and valuable selections in this way.

Some more examples:

Expression	What it does
Sum({$<Customer=e( {1<Product={'Bagel'}>} Customer)>} [Sales amount])	Select the total sales amount for the the current selections, for all customers that have never bought a bagel.
Sum({$<Customer=p( {1<TicketID={"=count(TicketID) > 0"}>} Customer)>} [Sales amount])	Select the total sales amount for the current selections, for all customers that have, at any point, filed a ticket.
Sum({$<Customer=p( {$<Product={'Bagel'}>} Customer)>} [Sales amount])	Select the total sales amount for the current selections, for all customers that have, within the current selections, also bought a bagel.
Sum({$<Customer=p( {1} Employee)>} [Sales amount])	Select the total sales amount for the current selections, for all customers that are also employees.

Assigning possible and excluded values from one field to another field

If you look closely at the last example in the table above you’ll notice something interesting. Rather than selecting possible values for customers, we’re selecting possible values for employees and applying them to the Customer field. Of course, we’re making the assumption that (at least some of) the values of the Employee field will match the Customer field.

In many cases, we can avoid the use of this feature by building a better data model. For the sake of this post, we’ll list a few use cases here:

• When using multiple master calendars, applying the selection from one master calendar to another master calender.

• When using a data island, applying the selection from a field in the data island to a field in the main model.

• When using alternate states, applying the selection from one alternate state to another alternate state.

That’s it for this week. See you next Friday?