Advanced Set Analysis

Sum( { set_identifier  < set_modifier > }  expression )
      ^                ^               ^
      |                |               |
  Set identifier   Set modifier   Aggregation
                                   expression
    

Set Identifiers

// Practical examples of identifiers
// Total sales - ignores any Region, Product, Year selection
Sum({1} Amount)

// Sales in current selection - identical to Sum(Amount) without Set Analysis
Sum({$} Amount)

// Sales under the previous selection
Sum({$_1} Amount)

// Sales under bookmark "BM01" state (e.g., Top 10 Customers)
Sum({BM01} Amount)

Types of Modifiers

// Defines exactly which values
// the field must have in the set

Sum({<Year={2024}>} Amount)

Sum({<Region={'South', 'West'}>} Amount)

Sum({<Status={'Active'}>,
     Year={2024}>} Amount)

// Overrides the current field selection
// and uses the specified values

// Removes the selection from a specific field
// while keeping other field selections intact

// Ignore Year selection, keep the rest
Sum({<Year=>} Amount)

// Ignore both Month AND Quarter, keep others
Sum({<Month=, Quarter=>} Amount)

// Practical for "full-year total" within
// a context filtered by week

Modifier with Expression-Derived Value

The most powerful modifier type: the set of values is determined by an expression evaluated at render time. Combines Set Analysis with dynamic logic.

// Current year derived from expression - dynamic, never needs updating
Sum({<Year={$(=Year(Today()))}>} Amount)

// Previous month calculated dynamically
Sum({<Month={$(=Month(AddMonths(Today(), -1)))},
     Year={$(=Year(AddMonths(Today(), -1)))}>} Amount)

// Top N values of the Product field (defined by variable vN)
// $(vN) expands to the variable value at calculation time
Sum({<ProductID=Top(Rank(Sum(Amount)), 1, $(vN))>} Amount)

// Dynamic period: from year start to today
Sum({<Date={">=$(=YearStart(Today()))<=$(=Today())"}>} Amount)

Modifiers with Partial Matching - Wildcards

// Products whose name starts with "Pro"
Sum({<Product={"Pro*"}>} Amount)

// Customers whose email is from the @company.com domain
Count({<Email={"*@company.com"}>} DISTINCT CustomerID)

// Records with code containing "US" in any position
Sum({<CountryCode={"*US*"}>} Amount)

// Combining wildcard with expression:
// Products that start with the user-selected letter
// (vLetter is a variable set by an input box)
Sum({<Product={"$(vLetter)*"}>} Amount)

// P(field) returns the set of POSSIBLE
// (non-excluded) values of a field
// in the current or a defined set

// Customers possible in the current selection
P(CustomerID)

// Products possible in set {1} filtered by Category
P({1}<Category={'Electronics'}> ProductID)

// Use: filter ANOTHER field by the possible values
Sum({<CustomerID=P({1}<Region={'West'}> CustomerID)>} Amount)
// "Sales of customers who are from the West region"

// E(field) returns the set of EXCLUDED
// (grayed out in Qlik) values of a field
// in the current selection

// Customers excluded by the current selection
E(CustomerID)

// Use: calculate over what was excluded
// (impact analysis - "what was left out")
Sum({<CustomerID=E(CustomerID)>} Amount)
// "Sales of customers who were excluded
//  by the current selection" - impact analysis

P() in Practice - Cohort Filtering

The most powerful use of P() is cross-field filtering. Instead of listing explicit values, you instruct Qlik to "use the values of field X that satisfy condition Y as the filter for field Z".

// Case 1: sales generated by customers who purchased a specific product
// Without P(): impossible without a JOIN in script
// With P(): elegant and direct

Sum({
  <CustomerID = P({1}<ProductID={'PROD-LAPTOP-001'}> CustomerID)>
} Amount)
// Step by step:
// 1. P({1}<ProductID={'PROD-LAPTOP-001'}> CustomerID)
//    returns all CustomerIDs who bought PROD-LAPTOP-001
// 2. <CustomerID = [that set of IDs]>
//    filters the data to those customers
// 3. Sum(Amount) over that filtered data

// Case 2: cross-sell analysis
// How much do Electronics buyers also spend in other categories?
Sum({
  <CustomerID = P({1}<Category={'Electronics'}> CustomerID),
   Category = {'Appliances'}>
} Amount)
// "Sales in the Appliances category
//  made by customers who also bought Electronics"

// Case 3: hierarchy - sales of managers whose reports hit their target
Sum({
  <ManagerID = P({1}<QuotaPercent={>100}> ManagerID)>
} BonusAmount)

Combining P() with Set Operators

// Cohort intersection: customers who purchased in BOTH periods
// (high-retention customers - bought in Q1 AND Q3)
Count({
  <CustomerID =
    P({1}<Quarter={'Q1'}> CustomerID)
    *
    P({1}<Quarter={'Q3'}> CustomerID)
  >
} DISTINCT CustomerID)

// Cohort difference: new customers in the current period
// (bought now, but did NOT buy in the previous period)
Sum({
  <CustomerID =
    P({1}<Year={$(=Year(Today()))}> CustomerID)
    -
    P({1}<Year={$(=Year(Today())-1)}> CustomerID)
  >
} Amount)
// "Revenue generated by first-time buyers this year"

Review Checklist - Set Analysis Part 1

• ✅
  Correct identifier: use {1} to ignore selections, {$} to respect them.
• ✅
  Numeric dollar-sign: use $(#variable) when the modifier is numeric, to avoid locale bugs.
• ✅
  Correct operator: + is union (OR), * is intersection (AND), - is exclusion.
• ⚠️
  P() needs explicit context: never write P(Field) without specifying {1} or {$} inside.
• ⚠️
  Double quotes for wildcards, single for strings: {"Pro*"} for wildcard, {'Product A'} for literal value.
• ℹ️
  Test with a small dataset: before using complex operators in production, validate with a reduced dataset where you know the expected result.