Logical functions

Overview

Use this category when you need conditions, comparisons, and branching logic in formulas. Logical functions help you build boolean-style expressions (typically represented as 1/0) and use them to select values (for example with IF(...) or to create flags for validation and analysis.

Start here if…

• You want to build a condition (thresholds, flags, rules).

• You want branching logic (if/else).

• You want to combine conditions.

• You want to detect missing/undefined values.

Not here if…

• You want comparison operators and boolean keywords as operators (for example >, =, AND, OR, NOT) → see Operators

• You want to filter a dataset to a subset of members → see Filtering & data shaping

• You want numeric transformations (rounding, abs, min/max) → see Math & numeric

Mental model

• Logical functions usually produce 1 (true) or 0 (false) across the same dimensionality as their inputs.

• Conditions are most commonly used inside IF to select between two results.

• Missing values can affect conditions; use IS_NA to create a diagnostic flag when results look unexpectedly empty or incomplete.

Common patterns

• Apply a business rule (if/else) → IF('Margin' < 0, 0, 'Margin')
  Use to cap, floor, or branch values based on a condition.

• Create a threshold flag → GT('Revenue', 100000)
  Use to mark where values exceed a threshold (returns 1/0).

• Compare two nodes for equality → EQ('Actuals', 'Plan')
  Use to create match/mismatch flags for validation.

• Combine multiple conditions → AND(GT('Node', 0), LT('Node', 10))
  Use to build “between” rules or multi-part checks.

• Invert a condition → NOT(EQ('Status', 1))
  Use when you want the opposite of an existing rule.

• Check for missing/undefined values → IS_NA('Node')
  Use to identify where values are undefined and may affect downstream comparisons.

Functions in this category

Choosing between similar functions

• IF vs flags (EQ/GT/LT/…)

  • Use EQ/GT/LT/… when you need a flag (1/0) for validation, filtering, or diagnostics.

  • Use IF when you want to return values (choose between two results).

• EQ vs NEQ

  • Use EQ to mark matches.

  • Use NEQ to mark mismatches.

• GT/GTE vs LT/LTE

  • Use GT/GTE for “above threshold” rules.

  • Use LT/LTE for “below threshold” rules.

• AND/OR vs XOR

  • Use AND when all conditions must hold.

  • Use OR when any condition may hold.

  • Use XOR when exactly one condition should hold.

• IS_NA vs FALSE/TRUE

  • Use IS_NA to detect undefined values in real data.

  • Use TRUE/FALSE when you need a constant condition across all defined intersections.

Pitfalls & troubleshooting

• IF result looks incomplete: check whether the condition is undefined for some intersections; use IS_NA to spot where inputs are missing.

• Cube size risk: IS_NA expands to a fully expanded cube and can hit maximum cube size.

• Conditions “look wrong”: spot-check both operands at the same intersection in Data preview (the root cause is often upstream values, not the comparison).

• Multi-part logic is hard to debug: split logic into helper nodes (one condition per node), then combine with AND / OR.

• Between logic: make it explicit with AND(GTE(...), LTE(...)) rather than relying on a single comparison.

• Missing vs zero confusion: review Troubleshooting guide (missing value behavior).

Related sections

• Operators comparisons and boolean logic used inside formulas

• Formula basics evaluation order and nesting inside-out

• Filtering & data shaping subset selection when conditions are used for filtering

• Math & numeric use MIN/MAX for simple caps/floors without IF

• Troubleshooting guide missing values and unexpected results