Complete Blox Formula Guide

This guide covers all aspects of writing formulas in Blox for FP&A and business planning models.

Table of Contents

  1. Formula Basics
  2. Syntax and Quoting
  3. Cross-Block References
  4. Filtering and Dimension Properties
  5. Function Categories
  6. Data Mapping System
  7. Best Practices and Tips

Formula Basics

Blox formulas support standard mathematical operations and follow familiar arithmetic rules. These are the building blocks for all calculations in your financial models.

Simple Arithmetic

Use basic mathematical operators to perform calculations between indicators, constants, and expressions:

Revenue - 'Cost of Sales'
'Units Sold' * Price
'Gross Margin' / Revenue

Constants and Numbers

You can include fixed numbers directly in formulas or combine them with indicators:

50000
Revenue + 1000
Price * 1.2

Parentheses for Order of Operations

Use parentheses to control the order of calculations and ensure your formulas compute correctly:

(Revenue - 'Variable Costs') / 'Units Sold'
Revenue * (1 + 'Growth Rate')

Syntax and Quoting

Proper quoting and syntax are essential for referencing indicators, blocks, and dimensions with spaces or special characters in their names.

When to Use Quotes

Wrap names containing spaces or special characters in single quotes to ensure proper parsing:

'Total Fixed Assets' + 'Total Current Assets'
'Profit & Loss'.'Net Profit'
'General Settings'.'VAT Rate'

Block and Dimension References

Use dot notation to reference items from other blocks or dimensions:

'Sales Block'.'Revenue'
Product.'Cost Price'
Customer.'Region'

Mixed Quotes and Dots

Combine quotes and dots when referencing complex names across blocks and dimensions:

'Sales Ops'.'Pipeline Revenue'
"Finance Register".'Cash'
'Workforce Costs'.'Total Workforce Costs'

Cross-Block References

Reference indicators from the current block or pull data from other blocks and dimensions to create comprehensive calculations.

Same Block References

Reference indicators within the current block by using their names directly:

Revenue
'Cost of Sales'
'Units Sold'

Other Block References

Pull data from indicators in different blocks using the 'Block Name'.'Indicator Name' syntax:

'Sales Block'.'Monthly Revenue'
'Workforce Planning'.'Headcount'
'Fixed Assets'.'Depreciation'

Dimension Property References

Access properties stored on dimension items, such as prices, rates, or other attributes:

Product.'Sales Price'
Employee.'Annual Salary'
Currency.'Exchange Rate'

Filtering and Dimension Properties

Filtering by Dimension Properties

Use square brackets to filter data by dimension properties. Important: Square bracket filters currently only work on external block references, not within the same block.

Filter by Dimension Item Name

This filters data to include only rows where the dimension matches a specific item name. Use this when you want data for a particular product, department, or other dimension item.

'Revenue Block'.'Monthly Revenue'['Product'.'Software']
'HR Block'.'Employee Costs'['Department'.'Engineering']
'Finance Block'.'Exchange Rates'['Currency'.'USD']

Filter by Property Value

You can filter by linking to a dimension property value:

'Pricing Block'.'Unit Price'['Product'.'Category'.'Premium']
'HR Block'.'Salary Data'['Department'.'Type'.'Operations']
'Finance Block'.'FX Rates'['Currency'.'Region'.'Europe']

Filter by Text Values

Filters perform exact text matching against property values stored in dimension properties:

'Sales Block'.'Regional Sales'['Region'.'Country'.'United States']
'Product Block'.'Cost Data'['Category'.'Type'.'Premium']
'HR Block'.'Employee Data'['Department'.'Location'.'New York']

Important: Filtering performs exact string matching only - no numeric ranges or pattern matching.

Same-Block Filtering Workaround

Since square bracket filters only work on external blocks, use this workaround to filter indicators within the same block by explicitly referencing the block name.

# Instead of: Revenue['Product'.'Premium']
# Use: 'Current Block'.Revenue['Product'.'Premium']

Property-Based Calculations

Multiply indicators by dimension properties to create dynamic calculations based on item properties:

'Units Sold' * Product.'Unit Price'
'Headcount' * Employee.'Annual Salary' / 12
'Revenue' * Currency.'Exchange Rate'

Function Categories

Blox provides specialized functions for different types of calculations. Each category serves specific business modeling needs.

Mathematical Functions

  • roundup(value): Round up to nearest integer
  • mod(dividend, divisor): Modulo/remainder operations
  • max(value1, value2, ...): Maximum value
  • min(value1, value2, ...): Minimum value
  • round(value, decimals): Round to specified decimal places

Time-Based Functions

  • balance(change, opening_balance): Running balance calculations
  • change(series, [initial_value]): Period-over-period changes
  • rolling_sum(series, from_offset, to_offset, [min_periods]): Moving window sums
  • prior(series, periods): Previous period values
  • defer(amount, periods): Defer revenue/costs over time
  • shift(series, periods): Shift values by periods

Conditional Functions

  • if(condition, true_value, false_value): Conditional logic
  • lookup(source, mapping_property): Cross-reference data

Period Functions

  • isstart(): First period indicator (no arguments)
  • isend(): Last period indicator (no arguments)
  • isactive(): Active period indicator (no arguments)
  • days(): Days in period (no arguments)
  • weekdays(): Weekdays in period (no arguments)

Roll-Forward Functions

  • rollfwd(series, [opening_value]): Roll forward balances
  • rampup(dimension, periods): Ramp up over periods

Examples by Category

Mathematical

roundup('Required Headcount')
mod(Month, 4)
max('Calculated Price', 'Minimum Price')

Time-Based

balance('Net Cash Flow', 50000)
change('Inventory Balance')
rolling_sum(Sales, -2, 0)
prior(Revenue, 12)
defer('Annual Contract', 12)

Conditional

if('Is Forecast' = 1, 'Forecast Values', 'Actual Values')
if(Revenue > 100000, Revenue * 0.1, 0)

Period Functions

'Opening Balance' * isstart()
'Closing Adjustment' * isend()
'Monthly Expenses' * isactive()
Revenue / days()

Data Mapping System

Understanding how Blox maps data between blocks with different dimensions is crucial for building accurate cross-block formulas.

How Blox Maps Data Between Blocks

Blox automatically handles data mapping between blocks using several intelligent strategies:

1. Direct Dimension Match

When source and target have the same dimensions:

'Sales'.'Revenue' → Maps directly by matching Product, Region, Time

2. Connected Dimensions

Using dimension properties to map between different structures:

lookup('Price List'.'Unit Price', Product.Category)

Maps Price List (by Category) to current block (by Product) using Product.Category property.

3. No Mapping (Broadcast)

When no mapping exists, values are replicated across all target items:

'Global Settings'.'Tax Rate' → Same value for all products/regions

4. Aggregation

When mapping from detailed to summary level:

'Product Sales' → 'Category Summary' (sums by category)

Mapping Examples

Product to Category Mapping

# Source: Product-level pricing
# Target: Category-level analysis
lookup('Product Pricing'.'Unit Cost', Product.Category)

Time Period Mapping

# Annual values to monthly
'Annual Budget' / 12

# Prior year comparison
prior('Revenue', 12)

Currency Conversion

'USD Revenue' * 'Exchange Rates'.'Rate'['Currency'.'GBP']

Best Practices and Tips

Function Nesting Rules

  • Most functions CANNOT be nested - use them standalone
  • if() statements CANNOT be nested:
if(Region = 'US', if(Product = 'Premium', Price * 1.2, Price), Price * 0.9)

Naming Conventions

  • Use clear, descriptive names: Monthly Recurring Revenue  not MRR_calc
  • Be consistent with naming patterns
  • Quote names with spaces or special characters

Formula Organization

# Good: Clear, readable
(Revenue - Variable Costs) / Units Sold

# Avoid: Hard to read
Revenue-Variable Costs/Units Sold

Avoiding Constants in Formulas

Strongly recommended: Avoid embedding constant numbers directly in formulas. Constants hide key assumptions and make models difficult to maintain and update.

Why Avoid Constants?

  • Hidden assumptions: Constants are not visible to model users
  • Hard to change: Updating assumptions requires editing formulas
  • Poor governance: No audit trail of assumption changes
  • Reduced flexibility: Cannot easily scenario test different values

Instead of Constants, Use Indicators

# Avoid: Hidden constant
Revenue * 1.15

# Better: Visible assumption
Revenue * 'Growth Rate'

# Avoid: Embedded tax rate
'Gross Profit' * 0.21

# Better: Separate indicator
'Gross Profit' * 'Tax Rate'

Acceptable Constants

Only use constants for mathematical conversions and universal factors:

Time Period Conversions:

'Annual Salary' / 12          # Annual to monthly
'Quarterly Revenue' * 4       # Quarterly to annual
'Weekly Hours' * 52           # Weekly to annual
'Daily Rate' * 365            # Daily to annual

Unit Conversions:

'Revenue in Thousands' * 1000 # Scale conversion
'Percentage Rate' / 100       # Percentage to decimal
'Square Meters' * 10.764      # Meters to square feet

Mathematical Constants:

'Radius Squared' * 3.14159    # Pi for area calculations
'Principal' * 2.718           # Euler's number (rare in business)

Best Practice Implementation

  1. Create assumption indicators for all business variables
  2. Group assumptions in dedicated blocks (e.g., "Global Assumptions")
  3. Document assumptions with clear names and descriptions
  4. Review assumptions regularly as part of model governance

Common Patterns

Revenue Recognition

defer('Annual Contract Value', 12)
balance('Deferred Revenue', Opening Balance)

Workforce Planning

'Headcount' * 'Annual Salary' / 12
balance('Headcount Changes', Opening Headcount)

Financial Ratios

'Gross Profit' / 'Revenue'
'EBITDA' / 'Revenue'
'Current Assets' / 'Current Liabilities'

Cash Flow

balance('Net Cash Flow', Opening Cash)
'Operating Cash Flow' + 'Financing Cash Flow' + 'Investing Cash Flow'

Performance Tips

  • Use specific dimension filters rather than broad calculations
  • Prefer direct references over complex lookups when possible
  • Group related calculations in the same block

Error Prevention

  • Always use parentheses for complex expressions
  • Test formulas with simple data first
  • Verify dimension mappings are working as expected
  • Check for circular references

Debugging Formulas

  1. Start with simple components
  2. Add complexity gradually
  3. Use intermediate calculations to isolate issues
  4. Verify data mappings with lookup functions

Real-World Formula Examples

These examples demonstrate common business calculations across different industries and use cases.

SaaS Metrics

# Monthly Recurring Revenue
'Active Customers' * 'Monthly Subscription Price'

# Customer Lifetime Value
'Average Revenue Per Customer' * 'Average Customer Lifetime'

# Churn Rate
'Customers Lost' / 'Opening Customers'

Financial Planning

# Cash Burn Rate
'Operating Expenses' - 'Revenue'

# Runway Calculation
'Current Cash' / 'Monthly Burn Rate'

# Working Capital
'Current Assets' - 'Current Liabilities'

Workforce Planning

# Total Compensation
'Base Salary' + 'Benefits' + 'Bonus'

# Effective Headcount
rollfwd('Headcount', 'Opening Headcount')

# Salary Increases
balance('Salary Changes', 'Base Salary')

Rollup Types (Data Types)

Each indicator in Blox has a rollup type (also called data_type) that determines how values are aggregated when summarizing data across dimensions or time periods. This is critical for proper formula behavior.

Available Rollup Types

number

  • Aggregation: Sum across dimensions, Sum over time
  • Use with: Most standard calculations, revenues, costs, quantities
  • Example: Revenue, Units Sold, Expenses

balance

  • Aggregation: Sum across dimensions, Last value over time
  • Use with: balance()  function for running balances
  • Example: Cash Balance, Inventory Levels, Loan Balances

opening_balance

  • Aggregation: Sum across dimensions, First value over time
  • Use with: rollfwd()  function for opening balances
  • Example: Opening Cash, Beginning Inventory

ratio

  • Aggregation: Mean across dimensions, Mean over time
  • Use with: Percentages, ratios, rates that should be recalculated at summary levels
  • Example: Gross Margin %, Conversion Rate, Tax Rate
  • Important: Cannot be used with complex formulas that reference other objects or use functions

statistic

  • Aggregation: Mean across dimensions, Mean over time
  • Use with: Statistical measures, averages
  • Example: Average Order Value, Mean Temperature

percentage

  • Aggregation: Mean across dimensions, Mean over time
  • Use with: Percentage values
  • Example: Growth Rate %, Market Share %

Rollup Type Guidelines

Critical Rules:

  • Use balance  rollup type with balance()  function
  • Use opening_balance  rollup type with rollfwd()  function
  • Use ratio  rollup type for KPIs that need recalculation at summary level
  • ratio  rollup type cannot be used with:
    • Complex formulas linking to other objects
    • Formulas requiring functions
    • Cross-block references

Examples of Proper Usage:

# Balance rollup with balance function
balance('Net Cash Flow', 50000)  // Indicator rollup type: balance

# Opening balance rollup with rollfwd function  
rollfwd(Headcount, 'Opening Headcount')  // Indicator rollup type: opening_balance

# Ratio rollup for simple ratios
'Gross Profit' / Revenue  // Indicator rollup type: ratio

# Number rollup for complex formulas
'Sales'.'Revenue' * Product.'Margin'  // Indicator rollup type: number

This guide provides the foundation for writing effective formulas in Blox. Start with simple expressions and gradually build complexity as you become more familiar with the syntax and functions.

Related Articles