skip to content

In Qlik, what does Sum({<Year={2023}>} Sales) return when the user has selected Year 2024?

level: middleimportance: must knowfreq 65%

answer

  1. only one expression changes, not the app
  2. the modifier names a field and overrides it
  3. what happens to the fields not named?
  4. assignment replaces, it does not intersect
  5. the empty element set clears a field's selection

basics

~10 s

It returns 2023 sales. The set modifier replaces the user's Year selection for that one aggregation only, while every other current selection still applies, and the rest of the app keeps showing 2024.

solid answer

~40 s

Set analysis defines the record set for a **single aggregation** by modifying the current selection state. `Sum({<Year={2023}>} Sales)` starts from the default state `$` — everything the user has selected — and overrides just the `Year` field with the literal value 2023. So if the user has selected Year 2024 and Country Sweden, the expression returns **Sweden's 2023 sales**: `Country` is untouched, `Year` is replaced. Nothing else in the app changes; other charts still show 2024. Two neighbouring forms matter: `{<Year=>}` with an empty element set *clears* the selection on `Year` (all years, other selections still applied), and `{1<...>}` starts from the full data set and ignores every user selection except what the modifier states. This is how you build year-over-year and share-of-total measures without touching the user's selections.

code

text · 7 lines
text
// User has selected Year = 2024 and Country = 'Sweden'

Sum(Sales)                          // Sweden, 2024
Sum({<Year={2023}>} Sales)          // Sweden, 2023   - Year replaced
Sum({<Year=>} Sales)                // Sweden, all years - Year selection cleared
Sum({1} Sales)                      // everything - all selections ignored
Sum({1<Country={'Sweden'}>} Sales)  // Sweden, all years - only the modifier applies

go deeper

for a junior

Recall the shape: braces inside the aggregation, angle brackets around a field, braces around the values. Know that it affects only that one expression and leaves the user's selections alone.

for a middle

Explain the evaluation: the default identifier is the current selection, an assignment replaces a field's selection rather than intersecting it, and unnamed fields are inherited. Be able to predict the number the expression returns.

for a senior

Show production judgment — factor recurring sets into variables, use dollar-sign expansion instead of hard-coded years, and diagnose the silent-zero case where an element set matches no real field value.

for a principal

Own the maintainability angle: dozens of hand-written set expressions across apps are duplicated business logic. Argue for master items, shared variables, or pushing the definition upstream before the same metric drifts between dashboards.

## Anatomy of a set expression A set expression sits inside an aggregation function, in braces, before the field being aggregated: ``` Sum( {<Year={2023}>} Sales ) ^ set expression ^ field ``` It has two parts. An **identifier** says what record set to start from, and one or more **modifiers** say how to change it. - `$` is the default identifier and means *the current selection*. It is implied when you write only a modifier, so `{<Year={2023}>}` is shorthand for `{$<Year={2023}>}`. - `1` means *the full data set*, ignoring every selection. - `$1` means the previous selection, one step back in the selection history, and a bookmark name or an alternate state name can also be used as an identifier. A modifier names a field and gives it an element set: `<Year={2023}>`. ## What the example does, step by step With the user having selected `Year = 2024` and `Country = 'Sweden'`: 1. Start from `$`: rows where Year is 2024 and Country is Sweden. 2. Apply `<Year={2023}>`: the `Year` field's selection is **replaced**, not intersected. Year becomes 2023. 3. Everything not named in the modifier is inherited. Country stays Sweden. Result: Sweden's 2023 sales, displayed next to charts that still show 2024. That is precisely the use case — a prior-year column, a budget column, a "same measure, different period" comparison — without asking the user to change selections. A very common wrong answer is "zero, because 2023 and 2024 conflict". They do not conflict, because assignment overrides rather than intersects. If you genuinely want an intersection you write it explicitly with `*=`. ## The operator family Inside a modifier, the assignment can be qualified: - `Year={2023}` — replace the selection on Year. - `Country+={'Norway'}` — union: keep the current Country selection and add Norway. - `Status-={'Cancelled'}` — subtract Cancelled from the current set. - `Product*={'Kayak','Canoe'}` — intersect the current selection with those values. And the empty element set is its own idiom: `Year=` clears the selection on `Year` entirely, leaving all years while other selections still apply. Candidates who know only `={value}` and `{1}` get stuck the moment the requirement is "ignore this one filter". ## Element sets: literals, searches and set functions Inside the braces you can put literal values, and quoting matters: single quotes denote a literal value (`{'Sweden'}`), while double quotes denote a **search** (`{"Swed*"}` or an expression search such as `{"=Sum(Sales)>10000"}`). Values can also come from another aggregation via a dollar-sign expansion, `{"$(=Max(Year))"}`, which is the standard way to say "the latest year in the possible data". The associative model shows through in two set functions: `P()` returns a field's possible values and `E()` returns its excluded values, both evaluated under an optional set of their own. `Sum({<Customer=P({<Product={'Kayak'}>})>} Sales)` reads as "total sales of customers who have ever bought a kayak" — an anti-join-shaped question expressed inline. ## Where it is evaluated A set expression is resolved **once for the chart object**, against the app's selection state — not once per row of a dimension. That surprises people who try to write a modifier that varies with the current dimension value. When you need a per-row calculation over a different grain, that is what `Aggr()` and the `TOTAL` qualifier are for; set analysis is the wrong tool for it. ## Do not cross-attribute this to other tools Set analysis modifies **selection state** for one aggregation in an in-memory associative model. It is not Power BI's `CALCULATE`, which manipulates filter context propagating over model relationships in DAX, and it is not a Tableau level-of-detail expression, which changes the *grain* at which an aggregate is computed rather than which selections apply. The vocabulary rhymes across the three tools and the mechanisms do not; saying "it's basically CALCULATE" in a Qlik interview is a recognised tell. ## Practical notes Field names are case-insensitive but the values must match the data exactly, including leading spaces — a modifier that silently matches nothing returns a blank or zero rather than an error, which is the usual reason a set expression "doesn't work". Because the expression is text, teams normally factor recurring sets into variables and expand them with `$(vSet)` to keep dozens of measures consistent. And a set expression only alters what the aggregation sees: filter panes, other charts and the selection bar still show what the user actually selected.

  • What does Sum({<Year=>} Sales) return, and how does it differ from Sum({1} Sales)?
    `{<Year=>}` uses an empty element set, which clears the selection on `Year` only: you get all years, but every other selection — country, product, customer — still applies. `{1}` starts from the full data set and ignores *all* selections. The first is "ignore this filter", the second is "ignore the user entirely", and confusing them is how share-of-total measures come out wrong.
  • How would you write a measure for sales to customers who have ever bought a specific product?
    Use the possible-values set function inside the modifier: `Sum({<Customer=P({<Product={'Kayak'}>})>} Sales)`. `P()` returns the customers possible under a kayak selection, and that set becomes the customer modifier for the outer aggregation. The mirror image uses `E()` for customers excluded by that selection — the "never bought it" cohort.
  • Why might a set expression silently return zero rather than raise an error?
    Element sets are matched against actual field values. A typo, a case-sensitive value mismatch, stray whitespace, or a date formatted differently from the field simply matches nothing, and an aggregation over no rows is null or zero. Check the field's real values first, and prefer a dollar-sign expansion such as `{"$(=Max(Year))"}` over hard-coded literals that drift.

saying these in an interview costs you the question

  • Says the expression returns zero because 2023 conflicts with 2024
  • Thinks a set modifier changes the user's selections app-wide
  • Cannot distinguish {1} from an empty element set
  • Describes set analysis as Qlik's version of CALCULATE
  • Believes the set expression is evaluated per dimension row

context

resolving links…