# Formula indicator (/docs/indicator-apps/indicators/formula) A **formula** indicator is a metric Ovidens works out for you from other indicators, instead of one you enter by hand. You build the calculation once - "revenue minus costs" - and Ovidens evaluates it for every period and dimension member automatically. Pick the **Formula** data type on the indicator and the **Calculation** panel appears in place of the collection settings. A calculation is evaluated **once per coordinate**. A coordinate is a single **period** (for example, March 2026) plus **one member per dimension** when the indicator has dimensions (for example, period March 2026 with Region "North"). Each reference in the calculation is resolved at that coordinate. If an input has no value there, the result is **empty**, unless you supply a fallback (see [Missing inputs](#missing-inputs)). New to indicator apps? Start with the [overview](../overview.mdx), which explains indicators, [dimensions and hierarchies](../dimensions/other-dimensions.mdx), and [periods and time grain](../dimensions/time.mdx). ## You build a calculation, you do not write one [#you-build-a-calculation-you-do-not-write-one] There is no formula language to learn. A calculation is built from a small set of **building blocks**, each chosen from a menu: * **Reference** - read another indicator. This is the only block that reads data. * **Number** - a value you type. * **Calculation** - two blocks with `+`, `-`, `*` or `/` between them. * **Absolute** - the magnitude of a block, without its sign. * **Rounded** - a block rounded to a number of decimal places. * **Fallback** - a block, with a second block used instead when the first is empty. * **Condition** - compare two blocks, and take one of two other blocks depending on the answer. Every block is picked from a dropdown, and every block can hold other blocks, so a calculation like "round renewable energy divided by total energy, times 100, to one decimal place" is built by nesting. The editor never asks you to type anything but a number, and there is nothing you can spell wrong. Beneath the blocks the editor shows two lines: what your calculation **reads as** in plain language, and the same calculation in Ovidens' own short notation. The first is the one to check your work against; the second is what error messages quote back to you. ## Let Vera build it for you [#let-vera-build-it-for-you] Click **Describe your metric**, type the calculation in plain words - for example "revenue minus costs" or "year-on-year growth in revenue" - and Vera builds it for you. What comes back is a real calculation in the editor, so you can open it up, change one part of it, and keep the rest. Ovidens checks Vera's answer before it reaches you, so a suggestion you accept is one that will save. ## Reading data: the Reference block [#reading-data-the-reference-block] A **Reference** block reads one other indicator. You choose the indicator from a list, and then choose **how** to read it - which is where the reference functions come in. You can reference a collected [numeric](./input.mdx#numeric) indicator, a collected [boolean](./input.mdx#boolean) indicator, or another formula indicator. A boolean reference resolves to a number: **yes becomes 1, no becomes 0**, and a missing value behaves exactly like a missing number. So `SUM` over a boolean counts how many periods met the target and `AVG` is the fraction that did. A [select](./input.mdx#select) indicator is compared with `SELECTED`, and a [text](./input.mdx#text) or [file](./input.mdx#file) indicator can only be asked whether it has been answered at all, with `HAS_VALUE` - both below. ### The reference functions [#the-reference-functions] | Function | What it reads | | ----------------------------------------------------------- | ------------------------------------------------------------------------------------------------ | | `VALUE` | the value at this coordinate | | `SUM` `AVG` `MIN` `MAX` `FIRST` `LAST` `COUNT` | a **finer-grained** indicator, rolled up to this indicator's period | | `ANCESTOR_SUM` `ANCESTOR_AVG` `ANCESTOR_MIN` `ANCESTOR_MAX` | a dimension rolled up **N levels above** the current member | | `LEVEL_SUM` `LEVEL_AVG` `LEVEL_MIN` `LEVEL_MAX` | a dimension rolled up to the **absolute level N** (1 is the top) | | `PERIOD_AGO` | the value **N periods back** | | `YEAR_AGO` | the value exactly **one year back** | | `DIM_SUM` | the **total across every member** of a dimension | | `MEMBER` | the value of **one named member** | | `SELECTED` | whether a **select** indicator holds one particular option - 1 if it does, 0 if it holds another | | `HAS_VALUE` | whether an indicator of **any type** has been answered - 1 or 0, never empty | | `MEMBER_COUNT` | **how many members** a dimension has at a level (this one names a dimension, not an indicator) | The editor only asks for the arguments the function you picked actually takes. Choose `PERIOD_AGO` and it asks how many periods; choose `MEMBER` and it asks which member; choose `VALUE` and it asks for nothing more. Change the function and the arguments follow - there is no way to leave an argument behind that the new function does not take. **Level numbers** are capped by the dimension itself. A hierarchy is at most five levels deep, so an ancestor distance is at most 4 and an absolute level at most 5; `MEMBER_COUNT` cannot name a level past the number of levels that dimension has. The editor offers only the levels that exist. **Time shifts.** For `PERIOD_AGO`, the count is a whole number of periods, at least 1. `YEAR_AGO` lands on the matching period one year earlier, with two small adjustments so there is always a period to compare against: a February 29 period compares against February 28 a year earlier, and when the earlier year has no 53rd week, a 53rd-week period compares against that year's last week instead. **Naming a dimension.** When an indicator is collected across more than one dimension, a roll-up needs to know which one. The editor asks - and when there is only one possibility it fills it in for you and says so. **Selecting a member.** `MEMBER` reads one slice. The member must be a **collection member**, that is, one at the level values are stored at; a parent member is a roll-up, which is what the `ANCESTOR_*` and `LEVEL_*` functions are for. Selecting one member collapses that dimension, so an indicator with no dimensions of its own can read a single member of a dimensioned indicator. **Reading a select.** `SELECTED` asks whether a select indicator holds **one particular option** - you pick the option from that indicator's own list. It answers 1 when the cell holds it, 0 when the cell holds a different one, and **nothing** when the cell has not been answered, because an unanswered question is unknown rather than "not that option". Wrap it in a **Fallback** if you want an unanswered cell to count as 0. The option is remembered by its own identity, not by its position, so **reordering an option list changes nothing** about the rules that name it. Removing an option a rule names is refused, with a message naming the option, rather than quietly leaving a rule that has stopped meaning anything. **Asking whether anything was answered.** `HAS_VALUE` works on an indicator of **any** type, including [text](./input.mdx#text) and [file](./input.mdx#file), and answers 1 or 0 at every cell - never empty, even where the indicator has no value stored there at all. It is what makes the commonest questionnaire rule expressible: *if you answered yes, you must attach the evidence*. ## Missing inputs [#missing-inputs] A missing input makes the **whole result empty**, and the **Fallback** block is the one way to say otherwise: it takes a block and a second block to use when the first comes out empty. Put a Fallback around a single Reference and it substitutes a number for that missing value; put it around a whole sub-calculation and it also covers the cases arithmetic itself produces - a division by zero, a result too large to represent. ## Worked examples [#worked-examples] Each example below is the **blocks you assemble**, and under each is the short notation Ovidens prints back beneath the editor once you have. You never type that line - it is there to read your work back to you, and it is the form an error message quotes. **Margin.** Both `revenue` and `costs` are monthly with the same dimensions, so each is a Reference read with `VALUE`. * **Calculation** with `-` between them * **Reference** -> `revenue`, read as `VALUE` * **Reference** -> `costs`, read as `VALUE` ``` ([revenue] - [costs]) ``` **Yearly total from monthly data.** Inside a yearly indicator, read the monthly series with a time aggregation. * **Reference** -> `monthly_sales`, read as `SUM` ``` SUM([monthly_sales]) ``` **Ratio with a divide-by-zero guard.** A **Condition** block returns 0 when the target is zero. * **Condition** * *If this*: **Reference** -> `target`, read as `VALUE` * *Compared with* `>`: **Number** -> 0 * *Then take*: **Calculation** with `/` between `actual` and `target` * *Otherwise take*: **Number** -> 0 ``` IF([target] > 0; ([actual] / [target]); 0) ``` **Year-over-year growth.** The same indicator read twice, once as it stands and once a year back. * **Calculation** with `/` between them * **Calculation** with `-`: **Reference** -> `revenue` (`VALUE`) and **Reference** -> `revenue` (`YEAR_AGO`) * **Reference** -> `revenue`, read as `YEAR_AGO` ``` (([revenue] - YEAR_AGO([revenue])) / YEAR_AGO([revenue])) ``` **Share of a total.** `energy_consumption` is collected across an `energy_source` breakdown. The renewable share is the renewable member over the total across all members, as a percentage, rounded to one place. * **Rounded** to 1 decimal place * **Calculation** with `*` * **Calculation** with `/`: **Reference** -> `energy_consumption` read as `MEMBER` of `renewable`, and **Reference** -> `energy_consumption` read as `DIM_SUM` * **Number** -> 100 ``` ROUND(((MEMBER([energy_consumption]; renewable) / DIM_SUM([energy_consumption])) * 100); 1) ``` With 120 renewable out of a 300 total, this computes 40. ## Grain rules [#grain-rules] A calculation's **grain** is its time grain (the period type) and its dimension scope (which dimensions, and at which level). References have to line up with that grain, or Ovidens refuses to save the indicator and says which reference does not fit. The time hierarchy, from finest to coarsest, is: ``` daily < weekly < monthly < quarterly < yearly ``` The rules: * A plain `VALUE` reference must be at the **same** time grain as the indicator. * A **finer** indicator has to be read with a time aggregation (`SUM`, `AVG`, `MIN`, `MAX`, `FIRST`, `LAST`, `COUNT`). * A **coarser** indicator is read as it stands and repeated at each finer coordinate. It is not divided up, so divide it yourself - `MEMBER_COUNT` is there for exactly that. * A reference collected at a **finer** hierarchy level on a shared dimension has to be rolled up with `ANCESTOR_*` or `LEVEL_*`. * A reference that keeps a dimension this indicator is not collected across cannot be compared with it, and Ovidens says which dimension. ## What Ovidens refuses, and when [#what-ovidens-refuses-and-when] **When you save.** Everything about how a calculation FITS is decided at save time, and every refusal names what did not fit: * a reference to an indicator this app does not hold, or to one that carries no numeric value; * a **cycle** - a calculation that, directly or through others, reads itself; * a **cadence** that does not reconcile: a finer reference with no aggregation, or an aggregation over a coarser one; * a **dimension** that cannot be lined up: an extra one the indicator is not collected across, or a finer level with no roll-up; * a **member** that does not exist, or is a parent rather than a collection member; * a **level** past the depth the dimension actually has; * a calculation that is simply **too large** - there is a cap on how many blocks one calculation may hold, and on how many references it may make. The editor asks the same question the save does, while you build, so a refusal reaches you as a red line under the calculation rather than as a failed save. **When the indicator is a formula.** A cell has a number or it has none. There is no per-cell reason, and that is deliberate rather than an omission: your calculation is ONE statement evaluated over the whole grid, so a blank cell is one whose inputs had no value there or whose arithmetic was undefined there, and the statement cannot tell those two apart without being run a second time to ask. What does get a reason is a calculation that could not run **at all**. Then no cell in the grid has a result, the reason is shown **once** above the grid, and each cell reads **No result** - so one sentence about the request is not repeated into every cell, and a cell with no result never looks like a cell containing zero. ## Where your calculation actually runs [#where-your-calculation-actually-runs] Every calculation becomes **one SQL statement** that Ovidens generates and keeps in step with your configuration. You can read it in full, and see the rows it produced, on the app's [pipeline](../pipeline.mdx) tab - which is what makes every figure in the app checkable. The statement is there to be **read**, not edited or run on its own: an indicator app's pipeline belongs to Ovidens, it is rewritten whenever your configuration changes, and each step runs only as part of the app's own evaluation. If the building blocks cannot express something you need, read the data into an **analytic app** with an [Indicator data](../../analytical-apps/pipeline/indicator-data.mdx) node. An analytic app has a real SQL editor with previews and typed output columns, and its statements are yours to write and keep.