Formula indicator

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).

New to indicator apps? Start with the overview, which explains indicators, dimensions and hierarchies, and periods and time grain.

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

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

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 indicator, a collected 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 indicator is compared with SELECTED, and a text or file indicator can only be asked whether it has been answered at all, with HAS_VALUE - both below.

The reference functions

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 and 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

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

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

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

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

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 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 node. An analytic app has a real SQL editor with previews and typed output columns, and its statements are yours to write and keep.