So far we have seen how the sets can be used to manipulate the result of an expression. To take the concept a bit further, we will now see how to use the P() and E() functions inside a Set Analysis expression. In the previous Set Analysis expressions, all field values were explicitly defined in the sets or variables or in certain cases through defined searches. The P() and E() functions make use of nested set definitions.
A P() function returns a set of all possible values while an E() function returns a set of all excluded values.
For the purpose of this recipe, we make use customer sales data as defined in the following inline data load. Load the following script in Qlik Sense data load editor:
P_E: LOAD * INLINE [ Customer,Month,Volume,Sales,Supplier ABC,Jan,100,10000,DEF ABC,Feb,100,10000,DEF ABC,Mar,400,12000,DEF ABC,Apr,100,10000,GHI DEF,Feb,200,25000,GHI DEF,Mar,300,25000,GHI DEF,Apr,200,25000,ABC GHI,Jan,200,8500,ABC GHI,Mar,200,7000,ABC GHI,Jun,200,5000,ABC ];
- On a new sheet, drag and drop the table object from the Assets panel on the left-hand side of the screen. Name the table as
Possible Sales. - Add Customer and Month as dimensions.
- Add the following expression for
Sales:Sum({$<Customer=P({1<Month={'Jan'}>})>}Sales) - Click on Save and
. - The resultant table will look similar to the following. Note that it only shows all the records for customers ABC and GHI:
- Next, create another table with the same dimensions, such as Customer and Month and name it
Excluded Sales. - Add the
Salesexpression as follows:Sum({$<Customer=E({1<Month={'Jan'}>})>}Sales) - The resultant table will look similar to the following screen shot. Note that we only have one customer DEF in the table:
- The
P()function selects all the possible values from the set. In the first expression:Sum({$<Customer=P({1<Month={'Jan'}>})>}Sales)We select the customers who have made sales in the month of January.
- However, the
E()function selects all the excluded values from the set. In the second expression:Sum({$<Customer=E({1<Month={'Jan'}>})>}Sales)We select the customers who have made sales in all months except January.
The concept of P() and E() can also be used with two fields for comparison inside the nested sets.
For example: if one needs to find out all those customers where the suppliers had a volume of 300, the set expression will be defined in the following way:
Sum({$<Customer=p({1<Volume={300}>}Supplier)>}Sales)Here, the element function P() returns a list of possible suppliers who had a volume of 300. The list of suppliers is then matched to the customers to make the relevant selections.
The resultant table will look similar to the following:
An E() function in place of P() will result in all the customers whose suppliers never had a volume of 300:
