Lesson 3 of 4 · 2 min
Testing and settings
Validate a query before anyone downstream sees it.
A query that runs without error isn't the same thing as a query that's correct.
Advanced settings expands to two areas: Update range, and Calculate future values if possible — a checkbox that lets the query extend into planned or forecast periods rather than stopping at the last actual. Update range is not a single behaviour but a dropdown with three genuinely different options, each revealing its own extra field once selected:
- On data availability — the query recalculates its full history whenever fresh source data arrives, no separate field needed. This is the one to reach for by default.
- Dynamic months — reveals an "Update last (n) months" stepper. Only that rolling window of recent months gets recalculated; everything older than the window is left alone even if the underlying source data changes.
- Fixed point — reveals a Fixed point month/year picker (e.g. Sep 2026). The query recalculates only up to that one anchored calendar point, regardless of how much new data lands afterward.
Along the bottom sit three controls together: Run query, a Status dropdown, and Create Query (or Save query once it already exists). Status has three values, not two: a new query opens on Draft; moving it to Active puts it live; Archived is the third — for a query nobody uses anymore but that should stay on record for documentation rather than being deleted outright. Archiving a query also clears its Assigned KPI back to unassigned, freeing that KPI up to be picked up by a different query, while the archived query's own canvas and formula stay exactly as they were, fully intact, just disconnected. The order that avoids trouble: build the calculation, run it and check the result against a number you already trust, adjust Advanced settings if the range or future-values behaviour isn't what you expected, and only then move Status to Active. Creating the query before running it is how a broken calculation ends up live on a dashboard.
- 1Update range — On data availability is the default of three options (with Dynamic months and Fixed point)
- 2Calculate future values if possible
- 3Run query, Status (Draft by default, also Active / Archived), Create Query
ValueWorks 2026.08
Takeaways
The order that matters: build → Run query → check against a trusted number → adjust Advanced settings → set Status to Active.
Update range has three modes: On data availability (full recalc on new data), Dynamic months (a rolling last-N-months window), Fixed point (frozen at one anchored month).
Status has three values: Draft, Active, Archived. Archiving keeps the formula intact for documentation but clears its Assigned KPI, freeing that KPI for another query.
Run query shows a table — the formula's own row plus one row per input component — with missing data reading "–", not an error.
Surfaces covered
- Run query
- Advanced settings → Update range
- Calculate future values if possible
- Validation result
Micro-check
Two quick questions
Practice only — nothing here is scored or recorded.
1. Put these in the right order: Set Status to Active, Run query, Build the calculation, Check the result against a trusted number.
2. You want a KPI to project into forecast months rather than stop at the last actual. Which setting?
3. A query should only ever recalculate its last three months, leaving everything older untouched even as source data changes. Which Update range setting?
4. A query hasn't been needed for months, but you want to keep its formula on record rather than delete it. What happens if you set its Status to Archived?