Periods, Indirect Sets
& Deep Nesting

Dynamic Period Comparisons

Hardcoding years in Set Analysis is the most common mistake in production dashboards. Every comparison - YTD, LY, QTD, Rolling 12M - must self-adjust as time moves forward.

The Cardinal Rule: Never Hardcode Dates

// Breaks every Jan 1st
Sum({<Year={2024}>} Sales)

// Needs manual update every year
Sum({<Year={2024},Month={<=3}>}
    Sales)

// Always current year
Sum({<Year={$(=Max(Year))}>} Sales)

// Always current year, Q1-present
Sum({$<Year={$(=Max(Year))},
    Month={$(=Month(Today()))}>}
    Sales)

YTD - Year to Date

Returns all data from January 1st of the current year through today's date:

// Pattern 1 - using Year + Month modifiers
Sum({$<Year={$(=Max({1}Year))},
     Month={"<=$(=Month(Today()))"}>} Sales)

// Pattern 2 - using Date range with YearStart()
Sum({$<Date={">=$(=YearStart(Today()))<$(=Today())"}>} Sales)

// Pattern 3 - most readable (recommended)
Sum({1<Date={$(=Chr(34)&YearStart(Today())&Chr(34)
           &"<"&Chr(34)&Today()&Chr(34)}>} Sales)

LY - Last Year

Same period as YTD but one year earlier. The key is subtracting 1 from the dynamic year:

// LY - same months as current YTD, prior year
Sum({$<Year={$(=Max({1}Year)-1)},
     Month={"<=$(=Month(Today()))"}>} Sales)

// YoY Growth % - complete KPI expression
Num(
    (Sum({$} Sales) / Sum({$<Year={$(=Max({1}Year)-1)}>} Sales)) - 1,
    '+#,##0.0%;-#,##0.0%;0.0%'
)

// Full year LY (not YTD-aligned)
Sum({1<Year={$(=Max({1}Year)-1)}>} Sales)

QTD - Quarter to Date

Current quarter only, dynamically computed from today's date:

// QTD - current quarter using QuarterStart()
Sum({$<Date={">=$(=QuarterStart(Today()))"}>} Sales)

// Quarter number modifier - cleaner for month-based models
Sum({$<Year={$(=Year(Today()))},
     Quarter={$(=Ceil(Month(Today())/3))}>} Sales)

// LQT - Last Quarter Total
Sum({1<Date={">=$(=QuarterStart(AddMonths(Today(),-3)))
              <$(=QuarterStart(Today()))"}>} Sales)

Rolling 12 Months (R12M)

The most powerful trend indicator - a true rolling window that moves with every passing month:

// Rolling 12M - last 365 days from today
Sum({$<Date={">=$(=AddMonths(Today(),-12))"}>} Sales)

// Rolling 12M - month-aligned (cleaner for YoY comparison)
Sum({$<Date={">=$(=MonthStart(AddMonths(Today(),-12)))
              <$(=MonthStart(Today()))"}>} Sales)

// R12M vs Prior R12M
R12M_Current:
Sum({$<Date={">=$(=AddMonths(Today(),-12))"}>} Sales)

R12M_Prior:
Sum({$<Date={">=$(=AddMonths(Today(),-24))
              <$(=AddMonths(Today(),-12))"}>} Sales)

Indirect Set Analysis

When the same filter logic appears in 15 expressions, changing one condition means 15 edits and 15 opportunities for bugs. Indirect Set Analysis solves this with variables as single sources of truth.

Pattern 1 - Field Value Variables

The simplest form: a variable holds a filter value used across multiple expressions.

// In the load script or Variables panel:
LET vActiveStatus = 'Active';
LET vKeyAccount  = 'Key Account';
LET vCurrentYear = Year(Today());

// Usage - clean and consistent across all objects
Sum({$<Status={'$(vActiveStatus)'}>} Revenue)
Count({$<CustomerTier={'$(vKeyAccount)'},Status={'$(vActiveStatus)'}>} DISTINCT CustomerID)
Sum({$<Year={'$(vCurrentYear)'}>} Revenue)

Pattern 2 - Full Set Analysis in a Variable

The variable holds the entire set modifier. This enables true single-point-of-change architecture:

// Variable contains the complete set modifier
LET vSetStrategic = "<CustomerTier={'Strategic'},Status={'Active'}>";
LET vSetYTD       = "<Year={'$(=Max({1}Year))'},Month={'<=$(=Month(Today()))'}>";

// These two expressions are now DRY - change the variable, update everywhere
Sum({$$(vSetStrategic)} Revenue)
Count({$$(vSetStrategic)} DISTINCT CustomerID)

Sum({$$(vSetYTD)} Revenue)
Avg({$$(vSetYTD)} TicketSize)

Pattern 3 - Toggle Mode with If()

The most powerful indirect pattern: a variable acts as a switch between two different data contexts. Critical rule: the If() must be at the expression level, not inside the aggregation.

// This does NOT toggle the Set context
// It filters rows AFTER aggregation
Sum(If(vMode=1,
    {<Status={'Active'}>} Rev,
    {<Status={'All'}>} Rev))

// This properly toggles the Set Analysis
If(vMode=1,
    Sum({<Status={'Active'}>} Revenue),
    Sum({<Status={'Churned'}>} Revenue)
)

Full toggle pattern with variable-controlled mode

// vDisplayMode is set by a button or input box (0 or 1)
// Button Action: Set variable vDisplayMode to '1' (or toggle)

// KPI expression with toggle
If(vDisplayMode = 0,

    // Mode 0: Current Year Performance
    Sum({$<Year={$(=Max({1}Year))}>} Revenue),

    // Mode 1: Prior Year Performance
    Sum({$<Year={$(=Max({1}Year)-1)}>} Revenue)
)

// Dynamic label for the KPI title
If(vDisplayMode = 0,
    'Revenue ' & Max({1}Year),
    'Revenue ' & (Max({1}Year)-1)
)

Pattern 4 - Parameterized Set with Multiple Variables

// Variables set by user input or app logic
LET vRegionFilter  = 'North America';
LET vChannelFilter = 'Online';
LET vProductFilter = '*';  // wildcard = all

// Single expression, parametrized via variables
Sum({$<
    Region   = {'$(vRegionFilter)'},
    Channel  = {'$(vChannelFilter)'},
    Product  = {'$(vProductFilter)'}
>} Revenue)

Nested Set Analysis

Nesting brings Set Analysis to its full power: P() and E() element functions can be composed with operators and modifiers, enabling cohort detection, cross-field filtering, and multi-dimensional analysis that would be impossible with simple modifiers.

Quick P() and E() Recap

// Customers who bought in 2024
CustomerID = P({<Year={2024}>} CustomerID)

// Customers who never bought in 2024
CustomerID = E({<Year={2024}>} CustomerID)

Multi-Level Nesting

P() inside P() - each level further restricts the cohort. Read inside out:

// Level 1: Customers who purchased Product A
// Level 2: From those, who also purchased Product B
// Count of those cross-buy customers in current selections
Count(
    {$<CustomerID =
        P({$<Product={'B'}>}
            P({$<Product={'A'}>} CustomerID)
         )
    >}
    DISTINCT CustomerID
)

// 3-level: Customers in Strategic tier, who bought in Q4,
// and who also bought in Q1 of the following year (loyalty signal)
Count(
    {$<CustomerID =
        P({$<Quarter={1},Year={$(=Max({1}Year))}>}
            P({$<Quarter={4},Year={$(=Max({1}Year)-1)}>}
                P({$<Tier={'Strategic'}>} CustomerID)
             )
         )
    >}
    DISTINCT CustomerID
)

P() Combined with Set Operators

// Customers who bought A OR B (Union)
Count({$<CustomerID =
    P({$<Product={'A'}>} CustomerID)
    +
    P({$<Product={'B'}>} CustomerID)
>} DISTINCT CustomerID)

// Customers who bought A AND B (Intersection)
Count({$<CustomerID =
    P({$<Product={'A'}>} CustomerID)
    *
    P({$<Product={'B'}>} CustomerID)
>} DISTINCT CustomerID)

// Customers who bought A but NOT B (Difference)
Count({$<CustomerID =
    P({$<Product={'A'}>} CustomerID)
    -
    P({$<Product={'B'}>} CustomerID)
>} DISTINCT CustomerID)

TOTAL Qualifier with Set Analysis

TOTAL bypasses chart dimensions but does not bypass Set Analysis. Combining them enables percentage-of-total calculations with full filter awareness:

// % share of each product within current selections
// (TOTAL still respects Set Analysis filters)
Sum({$} Revenue) /
Sum(TOTAL {$} Revenue)

// % of YTD sales per region, out of full YTD total
Sum({$<Year={$(=Max({1}Year))}>} Revenue) /
Sum(TOTAL {$<Year={$(=Max({1}Year))}>} Revenue)

// % vs unfiltered total (ignores ALL selections)
Sum({$} Revenue) /
Sum(TOTAL {1} Revenue)

Cross-Field P() - The Cohort Bridge

P() does not have to return the same field it filters on. This enables cross-field cohort bridges:

// Products purchased by customers in 'Strategic' tier
// (bridge: Customer filter → Product set)
Sum({$<ProductID =
    P({$<CustomerTier={'Strategic'}>} ProductID)
>} Revenue)

// Orders from cities where we have physical stores
Sum({$<CityID =
    P({1<HasPhysicalStore={'Yes'}>} CityID)
>} OnlineRevenue)

// Sales reps who hit quota last year
Count({$<SalesRepID =
    P({1<Year={$(=Max({1}Year)-1)},
         QuotaAchieved={'Yes'}>} SalesRepID)
>} DISTINCT OpportunityID)

Enterprise-Grade Expressions

Four battle-tested patterns you can drop into any Qlik Sense project. Each one combines the techniques from both parts of this series.

Pattern 1 - KPI Card with YoY Variation

// === KPI: Current Revenue ===
Sum({$} Revenue)

// === KPI: YoY % Variation (with color-coded formatting) ===
Num(
    (Sum({$} Revenue) /
     Sum({$<Year={$(=Max({1}Year)-1)}>} Revenue)) - 1,
    '+#,##0.0%;-#,##0.0%;0.0%'
)

// === Background color expression (green = positive, red = negative) ===
If(
    (Sum({$} Revenue) / Sum({$<Year={$(=Max({1}Year)-1)}>} Revenue)) - 1 > 0,
    RGB(34,197,94),   // Green
    RGB(239,68,68)    // Red
)

// === Trend icon ===
If(
    (Sum({$} Revenue) / Sum({$<Year={$(=Max({1}Year)-1)}>} Revenue)) - 1 > 0,
    '▲',
    '▼'
)

Pattern 2 - Active Customers & Churn Detection

// === Active customers: purchased in last 90 days ===
Count(
    {$<CustomerID =
        P({1<OrderDate={">=$(=Today()-90)"}>}
            CustomerID)
    >}
    DISTINCT CustomerID
)

// === Churned: bought last year but NOT in last 90 days ===
Count(
    {$<CustomerID =
        P({1<Year={$(=Max({1}Year)-1)}>} CustomerID)
        -
        P({1<OrderDate={">=$(=Today()-90)"}>} CustomerID)
    >}
    DISTINCT CustomerID
)

// === Churn rate ===
Num(
    Count({$<CustomerID =
        P({1<Year={$(=Max({1}Year)-1)}>} CustomerID)
        -
        P({1<OrderDate={">=$(=Today()-90)"}>} CustomerID)
    >} DISTINCT CustomerID)
    /
    Count({$<CustomerID =
        P({1<Year={$(=Max({1}Year)-1)}>} CustomerID)
    >} DISTINCT CustomerID),
    '#,##0.0%'
)

Pattern 3 - Pareto Analysis (80/20)

// === Running cumulative % share ===
// (sort the chart by Revenue descending)
RangeSum(Above(Sum({$} Revenue), 0, RowNo())) /
Sum(TOTAL {$} Revenue)

// === Flag products in top 80% ===
If(
    RangeSum(Above(Sum({$} Revenue), 0, RowNo())) /
    Sum(TOTAL {$} Revenue) <= 0.8,
    'Top 80%',
    'Long Tail'
)

// === Revenue from top 80% products ===
Sum({$<ProductID =
    P({$<ParetoCategory={'Top 80%'}>} ProductID)
>} Revenue)
// Note: ParetoCategory must be a field in the data model,
// or use a calculated dimension approach in a straight table

Pattern 4 - Budget vs. Actual with Completeness Guard

// === Actual (from selections) ===
Sum({$<RecordType={'Actual'}>} Amount)

// === Budget (locked - never filtered by user) ===
Sum({1<RecordType={'Budget'},
     Year={$(=Max({1}Year))}>} Amount)

// === Attainment % with null guard ===
If(
    Sum({1<RecordType={'Budget'}>} Amount) > 0,
    Num(
        Sum({$<RecordType={'Actual'}>} Amount) /
        Sum({1<RecordType={'Budget'}>} Amount),
        '#,##0.0%'
    ),
    'No Budget'
)

// === Gap (Actual minus Budget) ===
Sum({$<RecordType={'Actual'}>} Amount)
-
Sum({1<RecordType={'Budget'},
     Year={$(=Max({1}Year))}>} Amount)

Debug Techniques & Common Mistakes

Set Analysis expressions fail silently - instead of errors, they return 0 or NULL. Here is a systematic approach to isolate exactly where an expression breaks down.

3-Step Isolation Protocol

// Replace this...
Sum({$set} Revenue)
// ...with this first
Count({$set} CustomerID)

// Text box expression
'Max Year: '
  & Max({1}Year)
'Today: '
  & Today()
'vMyVar: '
  & $(vMyVar)

Count({1} OrderID)
Count({$} OrderID)
Count({$<Region={'US'}>} OrderID)
Count({$<Region={'US'},Year={2024}>} OrderID)

8 Common Mistakes and Fixes

Complete Mastery Checklist - Both Parts

• Understand set identifiers: {1}, {$}, {$_1}, named bookmarks
• Combine set operators (+, -, *, /) to build union, intersection, and complement sets
• Write modifiers using direct assignment, exclusion (=), expression-derived, and wildcards
• Apply dollar-sign expansion safely, including $(#var) for locale-safe numbers
• Use P() and E() for cohort filtering and cross-field bridges
• Write fully dynamic period comparisons (YTD, LY, QTD, R12M) that never require manual updates
• Implement indirect Set Analysis with variables as DRY filter containers
• Apply the If() toggle pattern at expression level - never inside the aggregation
• Compose multi-level P() nesting for advanced cohort logic
• Combine TOTAL qualifier with Set Analysis for correct % share calculations
• Build production KPIs: YoY variation, churn detection, Pareto, budget vs actual
• Debug silently failing expressions with the 3-step isolation protocol