Formulas

6.1 Overview

Formulas in Casabase Cube define calculated members, values that are computed at query time rather than stored in your fact table. Common examples include variance, variance percentage, year-over-year growth, gross margin, and custom KPIs.

Formulas are defined per cube and assigned to a target dimension. When a query includes a formula, the calculated member appears as an additional row in that dimension alongside your data-backed members.

For example, a formula named Variance on the YEARS hierarchy produces:

YEARS	AMT
Curr Year	100
Prev Year	80
Variance	20

6.2 Formula Editor (Streamlit UI)

The Formulas page provides a full-featured editor for creating, editing, and managing formulas. It is available to users with the CUBE_ADMIN role.

Creating a Formula

1. Navigate to Formulas in the sidebar
2. Select a cube from the dropdown
3. Fill in the formula fields:

Field	Required	Description
Formula Name	Yes	A unique name for the calculated member (e.g., Variance, Gross Margin %). This is the name that appears in query results.
Target Dimension	Yes	The dimension this formula belongs to. The formula appears as a member of this dimension.
Expression	Yes	The calculation logic. See syntax reference below.
Solve Order	No	Controls evaluation order when formulas depend on each other. Lower numbers evaluate first. Default is 0.

4. Use the Member Picker to browse and insert member references
5. Use the toolbar buttons for quick inserts ({ROW}, {TOTAL}, NULLIF, ABS, IFF, CASE)
6. Click Server Validate to check the formula for errors
7. Click Save Formula

Editor Features

Feature	Description
Syntax highlighting	Live preview with color-coded member references, functions, placeholders, and operators.
Member Picker	Search and browse members across all dimensions. Click to insert [DIMENSION].[Member] references.
Quick insert buttons	One-click insertion of common functions and placeholders.
Undo / Redo	Step through expression history.
Format	Auto-format CASE/WHEN expressions for readability.
Local validation	Real-time checks for unbalanced parentheses, brackets, and risky patterns (e.g., division without NULLIF).
Server validation	Validates the formula against the actual cube configuration, checking member references and syntax.
Diff view	When editing, shows a side-by-side comparison of original and modified expressions.

Managing Formulas

The formula list displays all formulas for the selected cube with the following actions:

Action	Description
Edit	Load a formula into the editor for modification.
Duplicate	Create a copy of a formula as a starting point for a new one.
Activate / Deactivate	Toggle whether a formula is included in queries. Inactive formulas are preserved but ignored.
Delete	Permanently remove a formula.
Move Up / Move Down	Adjust solve order relative to other formulas.
Filter	Search formulas by name, expression, or dimension.

6.3 Formula Syntax

Member References

Reference specific members from any dimension using bracket syntax:

[DIMENSION].[Member Name]

Examples:

[ACCOUNT].[Revenue]
[ACCOUNT].[Revenue] - [ACCOUNT].[Cost of Sales]
[YEARS].[CY] - [YEARS].[PY]

Cross-Dimensional References

Pull a value from a specific member in another dimension, regardless of the current row context:

[PERIOD].[Jan]                           -- Value at Jan, regardless of current period
[YEARS].[FY24]->[PERIOD].[Jan]           -- Value at FY24 and Jan intersection
[YEARS].[FY24]->[PERIOD].[Jan]->[ENTITY].[Corp]   -- Three-way intersection

Use the -> operator to chain multiple dimension references.

Tuple Syntax (Multi-Dimension Intersections)

An alternative to the -> chaining syntax, tuples let you reference a specific intersection across multiple dimensions using parentheses:

([TIME].[Qtr1], [YEARS].[Curr Year]) - ([TIME].[Qtr1], [YEARS].[Prev Year])

More examples:

([TIME].[YTD(Mar)], [YEARS].[Curr Year]) - ([TIME].[YTD(Mar)], [YEARS].[Prev Year])
([ACCOUNT].[Revenue], [ENTITY].[Corporate]) / NULLIF(([ACCOUNT].[Revenue], [ENTITY].[Total Entity]), 0) * 100

Row Placeholders

Placeholder	Description
{ROW}	The current row’s aggregated value.
{TOTAL}	The grand total across all rows in the result.
{TOTAL:DIM}	The total for the current member’s group within a specific dimension.
{TOTAL:DIM1,DIM2}	The total partitioned by multiple dimensions.
{FIRST:DIM}	The first value in the dimension (opening balance).
{LAST:DIM}	The last value in the dimension (closing balance).

Examples:

{ROW} / {TOTAL}                          -- Percentage of total
{ROW} / {TOTAL:ENTITY} * 100             -- Percentage within entity
{LAST:PERIOD} - {FIRST:PERIOD}           -- Change from opening to closing
{ROW} / {TOTAL:GEOGRAPHY,YEARS} * 100   -- Percentage within geography + year combination

SQL Functions

Formulas support standard Snowflake SQL functions:

Function	Example	Description
NULLIF(x, 0)	[ACCOUNT].[Revenue] / NULLIF([ACCOUNT].[Units], 0)	Returns NULL instead of dividing by zero.
NULLIFZERO(x)	{ROW} / NULLIFZERO({TOTAL})	Same as NULLIF(x, 0) — Snowflake shorthand.
COALESCE(a, b)	COALESCE([ACCOUNT].[Revenue], 0)	Returns the first non-NULL value.
IFF(cond, t, f)	IFF({ROW} > 0, 'Positive', 'Negative')	Conditional expression.
ABS(x)	ABS([ACCOUNT].[Variance])	Absolute value.
ROUND(x, n)	ROUND({ROW} / {TOTAL} * 100, 2)	Round to n decimal places.
SIGN(x)	SIGN([ACCOUNT].[Revenue])	Returns -1, 0, or 1.
POWER(x, n)	POWER(1 + {ROW}, 12) - 1	Raise to a power.
GREATEST(a, b)	GREATEST([ACCOUNT].[Revenue], [ACCOUNT].[Target])	Returns the larger value.
LEAST(a, b)	LEAST([ACCOUNT].[Budget], [ACCOUNT].[Forecast])	Returns the smaller value.
CASE WHEN	See examples below	Multi-condition logic.

CASE Expressions

Use CASE/WHEN for multi-condition logic:

CASE
  WHEN [ACCOUNT].[Revenue] = 0 THEN 0
  WHEN [ACCOUNT].[Revenue] > 0 THEN [ACCOUNT].[Profit] / [ACCOUNT].[Revenue] * 100
  ELSE NULL
END

6.4 IS* Functions (Hierarchy-Aware Formulas)

IS* functions allow formulas to adapt their calculations based on a member’s position in the hierarchy. This is especially useful for time-based calculations, allocation logic, and level-specific formatting.

Available Functions

Function	Returns	Description
ISLEAF(DIM)	TRUE/FALSE	TRUE if the member has no children (bottom level).
ISCHILD(DIM, 'parent')	TRUE/FALSE	TRUE if the member is a direct child of the specified parent.
ISDESC(DIM, 'ancestor')	TRUE/FALSE	TRUE if the member is a descendant of the specified ancestor.
ISANCEST(DIM, 'member')	TRUE/FALSE	TRUE if the current member is an ancestor of the specified member.
ISSIBLING(DIM, 'member')	TRUE/FALSE	TRUE if the current member is a sibling of the specified member.
ISPARENT(DIM, 'child')	TRUE/FALSE	TRUE if the current member is the parent of the specified child.
ISGEN(DIM, n)	TRUE/FALSE	TRUE if the member is at generation n (root = 1).
ISLEV(DIM, n)	TRUE/FALSE	TRUE if the member is at level n (leaf = 0).
PARENT(DIM)	VARCHAR	Returns the parent member’s name as a string.
GENERATION(DIM)	INTEGER	Returns the member’s generation number (root = 1).
LEVEL(DIM)	INTEGER	Returns the member’s level number (leaf = 0).
HASUDA(DIM, 'value')	TRUE/FALSE	TRUE if the member has the specified User Defined Attribute.

Examples

Annualize based on time granularity:

CASE
  WHEN ISLEAF(PERIOD) THEN {ROW} * 12
  WHEN ISCHILD(PERIOD, 'YearTotal') THEN {ROW} * 4
  ELSE {ROW}
END

Different logic for summary vs. detail levels:

CASE
  WHEN ISLEAF(ACCOUNT) THEN [ACCOUNT].[Revenue] - [ACCOUNT].[Cost]
  ELSE {ROW}
END

UDA-driven logic:

CASE
  WHEN HASUDA(ACCOUNT, 'EXPENSE') THEN {ROW} * -1
  ELSE {ROW}
END

Calculate only at leaf level (NULL at rollups):

IFF(ISLEAF(GEOGRAPHY), [MEASURES].[Revenue] / [MEASURES].[Units], NULL)

Filter by ancestry:

CASE WHEN ISDESC(PERIOD, 'Q1') THEN {ROW} * 1.1 ELSE {ROW} END

Check parent relationship:

IFF(PARENT(PERIOD) = 'Q1', 'Q1 Month', 'Other')
IFF(ISCHILD(ENTITY, 'Corporate'), 1, 0)

Generation-based logic:

CASE
  WHEN ISGEN(ACCOUNT, 1) THEN {ROW}
  WHEN ISGEN(ACCOUNT, 2) THEN {ROW} * 1.05
  ELSE {ROW} * 1.10
END

IS* Functions vs. Hardcoded Member Lists

Approach	Pros	Cons
IS* Functions	Self-documenting, hierarchy-aware, maintenance-free, adapts to hierarchy changes	Slightly more complex syntax
Hardcoded Lists	Simple to write initially	Breaks when members are added/removed, error-prone, hard to maintain

Always prefer IS* functions over hardcoded member lists.

6.5 Solve Order & Dependencies

When formulas reference other formulas, Casabase Cube needs to know what order to evaluate them. This is controlled by solve order.

How Solve Order Works

• Formulas with a lower solve order are evaluated first.
• A formula that depends on another formula must have a higher solve order than the formula it references.
• Formulas with the same solve order are evaluated independently (they cannot reference each other).

Example

Formula	Expression	Solve Order
Revenue	[ACCOUNT].[Product Revenue] + [ACCOUNT].[Service Revenue]	1
Cost	[ACCOUNT].[Direct Costs] + [ACCOUNT].[Indirect Costs]	1
Gross Margin	[ACCOUNT].[Revenue] - [ACCOUNT].[Cost]	2
Margin %	[ACCOUNT].[Gross Margin] / NULLIF([ACCOUNT].[Revenue], 0) * 100	3

In this example:

• Revenue and Cost are independent and share solve order 1.
• Gross Margin depends on Revenue and Cost, so it must be solve order 2 or higher.
• Margin % depends on Gross Margin and Revenue, so it must be solve order 3 or higher.

Dependency Detection

The Formulas page automatically detects:

• Circular dependencies: Formula A references B, and B references A. These are flagged as errors and must be resolved.
• Solve order conflicts: A formula referencing another formula with an equal or higher solve order. These are flagged as warnings.

6.6 Inline Formulas

Inline formulas are ad-hoc calculations passed directly in the FORMULA_LIST parameter of a query. They are not saved to the cube’s formula configuration and exist only for the duration of the query.

Syntax

Pass inline formula objects in the FORMULA_LIST parameter (5th argument to QUERY_CUBE):

[{"name": "Gross Margin", "expression": "[ACCOUNT].[Revenue] - [ACCOUNT].[COGS]", "hierarchy": "ACCOUNT"}]

When to Use Inline Formulas

Use Case	Description
One-off analysis	Quick calculations you do not need to save permanently.
Prototyping	Test a formula before adding it to the cube’s configuration.
Dynamic calculations	Formulas that change based on external parameters (e.g., generated by a script or application).
Query-specific logic	Calculations that only make sense in the context of a specific query.

Inline formulas can be combined with named formulas. Both named formula strings and inline formula objects are passed together in the FORMULA_LIST parameter:

["Variance", {"name": "Gross Margin", "expression": "[ACCOUNT].[Revenue] - [ACCOUNT].[COGS]", "hierarchy": "ACCOUNT"}]

Both will be evaluated together in the query.

6.7 Common Formula Patterns

Variance (Actual vs. Budget)

[SCENARIO].[Actual] - [SCENARIO].[Budget]

Variance Percentage

([SCENARIO].[Actual] - [SCENARIO].[Budget]) / NULLIF([SCENARIO].[Budget], 0) * 100

Year-over-Year Growth

[YEARS].[CY] - [YEARS].[PY]

Year-over-Year Growth Percentage

([YEARS].[CY] - [YEARS].[PY]) / NULLIF([YEARS].[PY], 0) * 100

Percentage of Total

{ROW} / NULLIF({TOTAL}, 0) * 100

Percentage of Parent

{ROW} / NULLIF({TOTAL:ACCOUNT}, 0) * 100

Gross Margin

[ACCOUNT].[Revenue] - [ACCOUNT].[Cost of Sales]

Gross Margin Percentage

([ACCOUNT].[Revenue] - [ACCOUNT].[Cost of Sales]) / NULLIF([ACCOUNT].[Revenue], 0) * 100

Safe Division (Avoid Divide-by-Zero)

IFF([ACCOUNT].[Units] = 0, 0, [ACCOUNT].[Revenue] / [ACCOUNT].[Units])

or equivalently:

[ACCOUNT].[Revenue] / NULLIF([ACCOUNT].[Units], 0)

Conditional Logic by Level

CASE
  WHEN ISLEAF(PERIOD) THEN {ROW} * 12
  WHEN ISGEN(PERIOD, 2) THEN {ROW} * 4
  ELSE {ROW}
END

CASE
  WHEN ISLEV(PERIOD, 0) THEN
    [ACCOUNT].[A_22000] / NULLIFZERO([ACCOUNT].[A_50000]) * 30
  WHEN ISLEV(PERIOD, 1) AND NOT (PERIOD = 'YearTotal') THEN
    [ACCOUNT].[A_22000] / NULLIFZERO([ACCOUNT].[A_50000]) * 90
  WHEN ISLEV(PERIOD, 2) OR (PERIOD = 'YearTotal') THEN
    [ACCOUNT].[A_22000] / NULLIFZERO([ACCOUNT].[A_50000]) * 360
  ELSE
    0
END

Cross-Dimensional Comparison

[YEARS].[FY24]->[PERIOD].[Jan] - [YEARS].[FY23]->[PERIOD].[Jan]

Absolute Variance

ABS([YEARS].[CY] - [YEARS].[PY])

Share / Contribution Analysis

{ROW} / {TOTAL:GEOGRAPHY} * 100             -- Percent within each geography
{ROW} / {TOTAL:GEOGRAPHY,YEARS} * 100       -- Percent within geography + year
{ROW} / [PRODUCTS].[All Merchandise] * 100   -- Percent of parent member

Time Intelligence (YTD / QTD)

YTD(Mar) = [TIME].[Qtr1]
YTD(Jun) = [TIME].[1st Half]
YTD(Sep) = [TIME].[1st Half] + [TIME].[Qtr3]
YTD(Dec) = [TIME].[1st Half] + [TIME].[Qtr3] + [TIME].[Qtr4]

QTD(Feb) = [TIME].[Jan] + [TIME].[Feb]
QTD(May) = [TIME].[Apr] + [TIME].[May]

Growth Rate

[YEARS].[CY] / NULLIFZERO([YEARS].[PY])

Index (Base 100)

([YEARS].[CY] / NULLIF([YEARS].[PY], 0)) * 100

Compound Annual Growth Rate (CAGR)

POWER([YEARS].[CY] / NULLIF([YEARS].[PY], 0), 0.5) - 1

Flags / Indicators

CASE
  WHEN [YEARS].[CY] > [YEARS].[PY] THEN 1
  WHEN [YEARS].[CY] < [YEARS].[PY] THEN -1
  ELSE 0
END

or equivalently:

SIGN([YEARS].[CY] - [YEARS].[PY])

6.8 Validation

Casabase Cube provides two levels of formula validation:

Local Validation (Automatic)

Runs automatically as you type in the editor. Checks for:

• Unbalanced parentheses or brackets
• Division without NULLIF/NULLIFZERO (divide-by-zero risk)
• Invalid SQL keywords (SELECT, INSERT, DROP, etc.)
• Unclosed function calls

Server Validation (On Demand)

Click Server Validate in the editor to validate against the actual cube configuration. Checks for:

• Member references that do not exist in the hierarchy
• Dimension references that do not exist in the cube
• Formula syntax errors that would cause query failures
• Dependency and solve order issues

Always run server validation before saving a formula to catch issues early.

6.9 Tips & Best Practices

Tip	Details
Always use NULLIF for division	Wrap divisors in NULLIF(x, 0) to avoid divide-by-zero errors. The editor warns you if you forget.
Use bracket syntax	Prefer [DIMENSION].[Member] over "DIMENSION"."Member". Bracket syntax is easier to read and avoids JSON escaping issues.
Start simple	Build formulas incrementally. Start with a simple member reference, validate it, then add complexity.
Test with inline formulas first	Prototype complex formulas as inline formulas in a query before saving them permanently.
Mind solve order	If Formula B references Formula A, ensure A has a lower solve order. The editor flags conflicts automatically.
Use IS* functions for level-aware logic	Instead of hardcoding member names in CASE expressions, use ISLEAF(), ISGEN(), and ISLEV() for logic that adapts as hierarchies change.
Keep formulas focused	Each formula should do one thing. Chain simple formulas using solve order rather than writing one complex expression.
Deactivate instead of deleting	If you are unsure whether a formula is still needed, deactivate it rather than deleting it. Inactive formulas are preserved but ignored in queries.