Churn (Customers with No Purchase)
All customers in the database (1), minus those with revenue > 0.
Count({1< ID=E({$} ID) >} Distinct ID)Sum({$} Sales)Excluded Values E()
Countries that are NOT in the list 'USA', 'Canada' (Excluded values function).
Sum({$< Country=E({1< Country={'USA','Canada'} >} Country) >} Sales)Sum({$< Year += {2025} >} Sales)Field Intersect (*=)
Keeps only the intersection of the current 'Year' selection with 2025.
Sum({$< Year *= {2025} >} Sales)Sum({$< Country={'USA'} >} Sales)Force Non-NULL
Excludes records where 'Region' is NULL by selecting all non-null values.
Sum({$< Region={"*"} >} Sales)Sum({1} Sales)Sum({$< Country= >} Sales)Sum({$ * MyBookmark} Sales)Sum({$< OrderDate = {">=$(=Date(Today()-30))Market Basket (A and B)
Customers who purchased both Product A AND Product B (intersection of P() sets).
Sum({$< Customer=P({1}) * P({1}) >} Sales)Market Share (%)
Ratio of current selection to total (TOTAL). Ignores chart dimensions.
Sum(Sales) / Sum({1} Total Sales)Sum({< OrderDate={">=$(=Date(MonthStart(Today())))Sum({$< Region = {'North', 'South'} >} Sales)Sum({$< Sales = {">1000Only NULL Values
Explicitly selects records where 'Region' is empty / NULL.
Sum({$< Region-={"*"} >} Sales)Possible Values P()
All countries that had sales in the 'North' region (Possible values function).
Sum({$< Country=P({1< Region={'Nord'} >} Country) >} Sales)Sum({$1} Sales)Prior Year Comparison (PY)
Data for the same period as the current selection, but for the previous year (Last Year, LY).
Sum({< Year={$(=Max(Year)-1)} >} Sales)Sum({$< Customer={"=Sum(Sales)>1000"} >} Sales)Sum({< OrderDate={">=$(=Date(AddMonths(Today(),-12)))Share of Group
Share of revenue within a group (TOTAL ). Ignores other chart dimensions.
Sum(Sales) / Sum({$} Total Sales)Sum({$ - 1} Sales)Top 10 Customers
Filters based on a calculated ranking using element functions. Returns the top 10 by revenue.
Sum({$< Customer={"=Rank(Sum(Sales))Sum({$ + 1} Sales)Sum({MyBookmark} Sales)Sum({$< Year={$(=vMaxYear)} >} Sales)Sum({$< Customer={"A*"} >} Sales)Sum({< Year={$(=Max(Year))}, MonthNum={"How to Use This Reference
Each card shows the expression name, a syntax snippet, and a difficulty badge. Click Details to see the full syntax, a working example with real field names, and links to related expressions.
Set Analysis Quick Syntax
The general form of a set analysis expression:
Sum( {<SetModifier>} FieldToAggregate ){1}: Ignore all selections (full data set).{$}: Current selections (default, same as no set modifier).{<Year={2026}>}: Force Year to 2026, keep other selections.{<Year=, Month=>}: Clear Year and Month selections, keep the rest.{1<Year={2026}>}: Ignore all selections, then force Year to 2026.
Related Resources
- Set Analysis Tutorial: In-depth guide to set analysis syntax, operators, and real-world patterns.
- Load Script Cheat Sheet: Script command reference for data loading and transformation.
- Qlik Extensions Catalog: Visualization extensions to display your expressions.
- Data Modeling Course: Article 17 covers expression optimization in depth.
Frequently Asked Questions
What is the difference between set analysis and an IF statement in Qlik?
Set analysis filters at the data model level before aggregation, which is significantly faster. An IF statement evaluates row by row during aggregation. For any expression that filters data, set analysis should be your default choice. Use IF only for conditional formatting or when you need to evaluate something that cannot be expressed as a set modifier.
Can I use variables inside set analysis?
Yes. Use dollar-sign expansion: Sum({<Year={$(vCurrentYear)}>} Sales). The variable is resolved before the expression is evaluated, so the set modifier receives the literal value.
How do I compare this year vs last year in one chart?
Create two measures. Current year: Sum({<Year={$(=Max(Year))}>} Sales). Previous year: Sum({<Year={$(=Max(Year)-1)}>} Sales). Both use set analysis to lock the year while respecting all other selections.