16 March 2023 Qlik Sense Set Analysis with the P() and E() functions Share this message 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: (click for larger version) 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: ExpressionWhat it doesSum({$<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? Take your Qlik skills to the next level! If you enjoy Qlik, you’ll love the Masters Summit for Qlik. 3 days of Qlik deep dives, peer networking, ready to use resources and much more! See you there! Test your Qlik knowledge! Check out the Friday Qlik Test Prep archive for more Qlik questions and answers. Friday Qlik Test Prep Set Analysis Solution How can we help? Feel free to contact us if you have any comments or questions. Call us Mail us 21 September 2023 Qlik Sense Enterprise for Windows – Critical Security Fix #2 A new security issue in Qlik Sense Enterprise for Windows has been identified. This resolves an incomplete fix for the vulnerability that was reported earlier this month. Patches are available and it is recommended to update your Qlik Sense Enterprise for Windows environment at the earliest possibility. New Release Qlik Security 6 September 2023 Battle Ships & Big Data: join us for some fun! Unleash the Power of Data in the Ultimate Battle Ship Game! Join us at the Big Data Expo for a thrilling experience that blends strategy, competition, and analytics. Discover how data influences gameplay and be part of our research! Plus, dive into the world of data and analytics with the experts from Bitmetric. Don’t miss out! See you at booth 46! Bitmetric Event Qlik Team 29 August 2023 Qlik Sense Enterprise for Windows – Critical Security Fixes Two security issues in Qlik Sense Enterprise for Windows have been identified and patches made available. Details can be found in Security Bulletin Critical Security fixes for Qlik Sense Enterprise for Windows (CVE-2023-41266, CVE-2023-41265). New Release Qlik Security
21 September 2023 Qlik Sense Enterprise for Windows – Critical Security Fix #2 A new security issue in Qlik Sense Enterprise for Windows has been identified. This resolves an incomplete fix for the vulnerability that was reported earlier this month. Patches are available and it is recommended to update your Qlik Sense Enterprise for Windows environment at the earliest possibility. New Release Qlik Security
6 September 2023 Battle Ships & Big Data: join us for some fun! Unleash the Power of Data in the Ultimate Battle Ship Game! Join us at the Big Data Expo for a thrilling experience that blends strategy, competition, and analytics. Discover how data influences gameplay and be part of our research! Plus, dive into the world of data and analytics with the experts from Bitmetric. Don’t miss out! See you at booth 46! Bitmetric Event Qlik Team
29 August 2023 Qlik Sense Enterprise for Windows – Critical Security Fixes Two security issues in Qlik Sense Enterprise for Windows have been identified and patches made available. Details can be found in Security Bulletin Critical Security fixes for Qlik Sense Enterprise for Windows (CVE-2023-41266, CVE-2023-41265). New Release Qlik Security