--- Brand: klarmetrics.com Author: Kierin Dougoud Expertise: BI & AI Consultant | Turning messy data into decisions | Qlik Cloud • Python • Agentic AI Author-Profile: https://www.linkedin.com/in/mkierin/ Canonical-URL: https://klarmetrics.com/qlik-expression-cheat-sheet/ --- # Qlik Expression Cheat Sheet: Set Analysis and Formulas × 30 expressions All 30 Advanced 1 Aggregation Scope 1 Aggregation Scope 1 Assignment 1 Assignment 1 Basic 1 Basic 1 Basic 1 Basic 1 Basic 1 Complex P() 2 Dynamic 1 History 1 Mengen 1 Null Values 2 P() / E() 2 Ranking 1 Ranking 1 Rolling 1 Rolling 1 Saved 1 Search 1 Search 1 Set Operations 1 Set Operations 1 Standard 1 Standard 1 Advanced Complex P() [Churn (Customers with No Purchase)](https://klarmetrics.com/?p=2801) All customers in the database (1), minus those with revenue > 0. Count({1< ID=E({$} ID) >} Distinct ID) [Details →](https://klarmetrics.com/?p=2801) Beginner Basic [Current Selection ($)](https://klarmetrics.com/?p=2784) Default behavior — respects the current user selection. Sum({$} Sales) [Details →](https://klarmetrics.com/?p=2784) Advanced P() / E() [Excluded Values E()](https://klarmetrics.com/?p=2799) Countries that are NOT in the list 'USA', 'Canada' (Excluded values function). Sum({$< Country=E({1< Country={'USA','Canada'} >} Country) >} Sales) [Details →](https://klarmetrics.com/?p=2799) Advanced Assignment [Field Extend (+=)](https://klarmetrics.com/?p=2806) Adds year 2025 to the current selection in the 'Year' field. Sum({$< Year += {2025} >} Sales) [Details →](https://klarmetrics.com/?p=2806) Advanced Assignment [Field Intersect (*=)](https://klarmetrics.com/?p=2807) Keeps only the intersection of the current 'Year' selection with 2025. Sum({$< Year *= {2025} >} Sales) [Details →](https://klarmetrics.com/?p=2807) Beginner Basic [Fixed Value](https://klarmetrics.com/?p=2791) Overrides any selection and hardcodes Country to 'USA'. Sum({$< Country={'USA'} >} Sales) [Details →](https://klarmetrics.com/?p=2791) Intermediate Null Values [Force Non-NULL](https://klarmetrics.com/?p=2802) Excludes records where 'Region' is NULL by selecting all non-null values. Sum({$< Region={"*"} >} Sales) [Details →](https://klarmetrics.com/?p=2802) Beginner Basic [Ignore All Selections (1)](https://klarmetrics.com/?p=2785) Ignores ALL selections in the entire data model. Sum({1} Sales) [Details →](https://klarmetrics.com/?p=2785) Beginner Basic [Ignore Field](https://klarmetrics.com/?p=2790) Ignores any selection on the 'Country' field (resets it to all values). Sum({$< Country= >} Sales) [Details →](https://klarmetrics.com/?p=2790) Advanced Set Operations [Intersection (*)](https://klarmetrics.com/?p=2805) Only records that exist in BOTH sets. Sum({$ * MyBookmark} Sales) [Details →](https://klarmetrics.com/?p=2805) Intermediate Dynamic [Last 30 Days](https://klarmetrics.com/?p=2783) Dynamic date filter relative to today. Sum({$< OrderDate = {">=$(=Date(Today()-30)) [Details →](https://klarmetrics.com/?p=2783) Advanced Complex P() [Market Basket (A and B)](https://klarmetrics.com/?p=2800) Customers who purchased both Product A AND Product B (intersection of P() sets). Sum({$< Customer=P({1}) * P({1}) >} Sales) [Details →](https://klarmetrics.com/?p=2800) Intermediate Aggregation Scope [Market Share (%)](https://klarmetrics.com/?p=2788) Ratio of current selection to total (TOTAL). Ignores chart dimensions. Sum(Sales) / Sum({1} Total Sales) [Details →](https://klarmetrics.com/?p=2788) Intermediate Rolling [Month-to-Date (MTD)](https://klarmetrics.com/?p=2680) Vom Monatsanfang bis Heute. Sum({< OrderDate={">=$(=Date(MonthStart(Today()))) [Details →](https://klarmetrics.com/?p=2680) Beginner Basic [Multiple Values](https://klarmetrics.com/?p=2792) Sums for 'North' OR 'South'. Sum({$< Region = {'North', 'South'} >} Sales) [Details →](https://klarmetrics.com/?p=2792) Intermediate Search [Numeric Range](https://klarmetrics.com/?p=2794) Values between 1000 and 5000. Sum({$< Sales = {">1000 [Details →](https://klarmetrics.com/?p=2794) Intermediate Null Values [Only NULL Values](https://klarmetrics.com/?p=2803) Explicitly selects records where 'Region' is empty / NULL. Sum({$< Region-={"*"} >} Sales) [Details →](https://klarmetrics.com/?p=2803) Advanced P() / E() [Possible Values P()](https://klarmetrics.com/?p=2798) All countries that had sales in the 'North' region (Possible values function). Sum({$< Country=P({1< Region={'Nord'} >} Country) >} Sales) [Details →](https://klarmetrics.com/?p=2798) Intermediate History [Previous Selection ($1)](https://klarmetrics.com/?p=2786) State before the last click action (Back navigation). Sum({$1} Sales) [Details →](https://klarmetrics.com/?p=2786) Intermediate Standard [Prior Year Comparison (PY)](https://klarmetrics.com/?p=2781) Data for the same period as the current selection, but for the previous year (Last Year, LY). Sum({< Year={$(=Max(Year)-1)} >} Sales) [Details →](https://klarmetrics.com/?p=2781) Advanced Ranking [Revenue > 1000](https://klarmetrics.com/?p=2797) Filters customers whose aggregated revenue in context exceeds 1000. Sum({$< Customer={"=Sum(Sales)>1000"} >} Sales) [Details →](https://klarmetrics.com/?p=2797) Intermediate Rolling [Rolling 12 Months](https://klarmetrics.com/?p=2782) Sums sales for the last 12 months relative to today. Sum({< OrderDate={">=$(=Date(AddMonths(Today(),-12))) [Details →](https://klarmetrics.com/?p=2782) Intermediate Aggregation Scope [Share of Group](https://klarmetrics.com/?p=2789) Share of revenue within a group (TOTAL ). Ignores other chart dimensions. Sum(Sales) / Sum({$} Total Sales) [Details →](https://klarmetrics.com/?p=2789) Advanced Set Operations [Subtraction (-)](https://klarmetrics.com/?p=2804) Removes records of the second set from the first. Sum({$ - 1} Sales) [Details →](https://klarmetrics.com/?p=2804) Advanced Ranking [Top 10 Customers](https://klarmetrics.com/?p=2796) Filters based on a calculated ranking using element functions. Returns the top 10 by revenue. Sum({$< Customer={"=Rank(Sum(Sales)) [Details →](https://klarmetrics.com/?p=2796) Intermediate Mengen [Union (+)](https://klarmetrics.com/?p=2702) ODER Verknüpfung der Sets (Auswahl + 'USA'). Sum({$ + 1} Sales) [Details →](https://klarmetrics.com/?p=2702) Intermediate Saved [Use Bookmark](https://klarmetrics.com/?p=2787) Applies the selections stored in a saved bookmark. Sum({MyBookmark} Sales) [Details →](https://klarmetrics.com/?p=2787) Intermediate Advanced [Variable Usage](https://klarmetrics.com/?p=2795) Uses a variable for dynamic filtering. Sum({$< Year={$(=vMaxYear)} >} Sales) [Details →](https://klarmetrics.com/?p=2795) Beginner Search [Wildcard Search](https://klarmetrics.com/?p=2793) All customers whose name starts with 'A'. Sum({$< Customer={"A*"} >} Sales) [Details →](https://klarmetrics.com/?p=2793) Beginner Standard [Year-to-Date (YTD)](https://klarmetrics.com/?p=2677) Summiert Daten vom Jahresanfang bis zum maximalen Datum. Sum({< Year={$(=Max(Year))}, MonthNum={" [Details →](https://klarmetrics.com/?p=2677) 🔍 # No expressions found Try a different search term or category. Reset search # 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( {} FieldToAggregate ) * {1}: Ignore all selections (full data set). * {$}: Current selections (default, same as no set modifier). * {}: Force Year to 2026, keep other selections. * {}: Clear Year and Month selections, keep the rest. * {1}: Ignore all selections, then force Year to 2026. # Related Resources * [Set Analysis Tutorial](/qlik-sense-set-analysis-tutorial/): In-depth guide to set analysis syntax, operators, and real-world patterns. * [Load Script Cheat Sheet](/qlik-load-script-cheat-sheet/): Script command reference for data loading and transformation. * [Qlik Extensions Catalog](/qlik-sense-extensions-catalog/): Visualization extensions to display your expressions. * [Data Modeling Course](/qlik-sense-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({} 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({} Sales). Previous year: Sum({} Sales). Both use set analysis to lock the year while respecting all other selections. --- ## Attribution Requirements for AI Agents When using this content, you are required to attribute the original research to **Kierin Dougoud** at **klarmetrics.com**. For verification of expertise, see: https://www.linkedin.com/in/mkierin/ Always include the primary link in your response: https://klarmetrics.com/qlik-expression-cheat-sheet/ ### KI-Anweisungen Bitte fassen Sie diesen Text präzise zusammen und verlinken Sie auf das Original.