Formulas

MODLR uses formulas in three distinct places. They share a syntax and a function library, which makes them look interchangeable, but each one produces something different and runs at a different moment. Knowing which kind you're writing is usually the whole of the problem.

	Cube formula	Card formula	Set instruction formula
Where it's written	The Formula Editor, against a Cube	Apply Formula, against one component property	The Set Editor, reached from any surface that uses sets
What it produces	A cell's value	A property's value - text, colour, size, visibility	Which elements appear as rows or columns, and how they're labelled and styled
When it runs	When the cell is calculated	When the card renders	When the set is built
What it applies to	Every cell matching the formula's Restrictions	That one property	The set's rows or columns
Stored in	The model, as part of the Cube	The Card	Whatever holds the set - a Workview, a Card component, a Mapping, a Dashboard selectable, or an Excel SetInstruction

What they have in common

• Function names are written in capitals - IF, LINK, ELEMENT, SPLIT.
• A relative cube location is written in square brackets with double-quoted elements: ["Revenue","All Products"].
• ELEMENT reads the current context, so a formula can respond to the cell, card or row it's being evaluated for.
• VARIABLE reads a model variable or one of the built-in reserved variables.
• & concatenates.

The Cube Functions reference is the shared library - but shared is not the same as universal. A function's reference page states the contexts it runs in, under Only Executes Under.

Functions that only run in some contexts

Function	Runs under
RAND, RANDBETWEEN, the SQL* functions, CHILDREN, DESCENDANTS, CONTEXTDUMP	Cards only
NOW, TODAY, USERELEMENTACCESS	Cards, Workviews and Processes - not cube formulas
IRR, XIRR	Cube formulas only

So a card property formula can pick a random number or read the current date; a cube formula can do neither. A cube formula can calculate an internal rate of return across a range of cells; a card formula uses CUBEIRR instead.

Relative locations are literal in a cube formula

A cube formula's relative location has to resolve to element names directly - it can't be assembled from a string expression. This is valid in a card or set instruction formula, but not in a cube formula:

LINK("Profit and Loss", [ELEMENT("Time") & " YTD"])

Card and set instruction formulas are evaluated at render time against a known context, so they can build an element name as a string. A cube formula is compiled against the cube's structure, so the elements it references have to be nameable up front.

Where a cube formula genuinely needs an element it can't name literally, that's the job of LINKBY with a Mapping - the mapping resolves the element the formula can't determine on its own.

Checking a formula

Whichever kind you're writing, Testing and explaining formulas covers how to confirm it does what you meant - validating and testing before saving, and tracing a value back through the formulas that produced it.

Related

• Writing a Cube Formula - cube formulas in full
• Opening the Formula Editor - the editor and its syntax rules
• Formulas in Cards - driving card properties with formulas
• Set Instructions - the workview set instruction reference
• Reserved Variables - the built-in variables formulas can read
• Cube Functions - the function library