Calculated Fields Functions Reference

This document provides a comprehensive reference for all available functions in the pyQueryHub calculated fields feature. The formula engine is compatible with Mode Analytics syntax and supports 70+ functions across multiple categories.

Table of Contents

1. Syntax Overview
2. Number Functions
3. String Functions
4. Date Functions
5. Conversion Functions
6. Logical Functions
7. Aggregate Functions
8. Analytical Functions
9. Examples and Use Cases
10. Best Practices
11. Troubleshooting

Syntax Overview

Basic Syntax Rules

• Field References: Use square brackets [field_name]
• String Literals: Use single or double quotes 'text' or "text"
• Numbers: Use decimals directly 123 or 45.67
• Operators: Standard math and comparison operators +, -, *, /, ==, !=, >, <, >=, <=
• Functions: Function name followed by parentheses SUM([field])
• Grouping: Use parentheses to control order of operations ([a] + [b]) * [c]

Formula Examples

-- Simple calculation
[revenue] - [cost]

-- Conditional logic
CASE [status] WHEN 'active' THEN 1 ELSE 0 END

-- Aggregation with condition
SUM(CASE [region] WHEN 'North' THEN [sales] ELSE 0 END)

-- String manipulation
UPPER(LEFT([customer_name], 5))

-- Date calculation
DATEDIFF('day', [order_date], TODAY())

Number Functions

ABS(number)

Returns the absolute value of a number.

Syntax: ABS(expression)

Examples:

ABS(-25)           -- Returns: 25
ABS([profit])      -- Returns absolute value of profit field
ABS([sales] - [target])  -- Returns absolute difference

Use Cases:

• Calculate absolute differences
• Remove negative signs from values
• Distance calculations

CEILING(number)

Rounds a number up to the nearest integer.

Syntax: CEILING(expression)

Examples:

CEILING(3.2)       -- Returns: 4
CEILING(-2.8)      -- Returns: -2
CEILING([price] / 10)  -- Round up price divided by 10

Use Cases:

• Calculate minimum required units
• Round up pricing tiers
• Capacity planning

EXP(number)

Returns e (Euler's number) raised to the power of the specified number.

Syntax: EXP(expression)

Examples:

EXP(1)             -- Returns: 2.718281828...
EXP(2)             -- Returns: 7.389...
EXP([growth_rate]) -- Exponential growth calculation

Use Cases:

• Exponential growth modeling
• Statistical calculations
• Compound interest calculations

FLOOR(number)

Rounds a number down to the nearest integer.

Syntax: FLOOR(expression)

Examples:

FLOOR(3.8)         -- Returns: 3
FLOOR(-2.2)        -- Returns: -3
FLOOR([revenue] / 1000)  -- Round down to thousands

Use Cases:

• Calculate completed units
• Tier-based pricing
• Age calculation in years

LOG10(number)

Returns the base-10 logarithm of a number.

Syntax: LOG10(expression)

Examples:

LOG10(100)         -- Returns: 2
LOG10(1000)        -- Returns: 3
LOG10([population])  -- Logarithmic scale for large numbers

Use Cases:

• Data normalization
• Scientific calculations
• Logarithmic scales

LN(number)

Returns the natural logarithm (base e) of a number.

Syntax: LN(expression)

Examples:

LN(2.718281828)    -- Returns: 1 (approximately)
LN([value])        -- Natural log of value

Use Cases:

• Statistical analysis
• Growth rate calculations
• Mathematical modeling

MOD(number, divisor)

Returns the remainder after division.

Syntax: MOD(dividend, divisor)

Examples:

MOD(10, 3)         -- Returns: 1
MOD([order_id], 2) -- Returns 0 for even IDs, 1 for odd
MOD([day_number], 7)  -- Day of week (0-6)

Use Cases:

• Determine even/odd numbers
• Cycle calculations
• Pagination logic

POWER(base, exponent)

Returns a number raised to a specified power.

Syntax: POWER(base, exponent)

Examples:

POWER(2, 3)        -- Returns: 8
POWER([base], [exp])  -- Base raised to exp power
POWER([sales], 2)  -- Square of sales values

Use Cases:

• Area and volume calculations
• Exponential scaling
• Mathematical formulas

ROUND(number, precision)

Rounds a number to a specified number of decimal places.

Syntax: ROUND(expression, decimal_places)

Examples:

ROUND(3.14159, 2)  -- Returns: 3.14
ROUND([price], 0)  -- Round to nearest whole number
ROUND([percentage] * 100, 1)  -- Round percentage to 1 decimal

Use Cases:

• Financial calculations
• Display formatting
• Precision control

SQRT(number)

Returns the square root of a number.

Syntax: SQRT(expression)

Examples:

SQRT(9)            -- Returns: 3
SQRT(25)           -- Returns: 5
SQRT([area])       -- Calculate side length from area

Use Cases:

• Geometric calculations
• Standard deviation formulas
• Distance calculations

TRUNC(number, precision)

Truncates a number to a specified number of decimal places.

Syntax: TRUNC(expression, decimal_places)

Examples:

TRUNC(3.14159, 2)  -- Returns: 3.14
TRUNC([value], 0)  -- Remove decimal portion
TRUNC([price] * 1.1, 2)  -- Truncate calculated price

Use Cases:

• Remove decimal precision
• Data cleaning
• Fixed-point calculations

ZN(expression)

Returns the expression if it's not null, otherwise returns 0.

Syntax: ZN(expression)

Examples:

ZN([commission])   -- Returns 0 if commission is null
ZN([bonus]) + [salary]  -- Add bonus or 0 to salary

Use Cases:

• Handle null values in calculations
• Default to zero for missing data
• Prevent null propagation

String Functions

CONTAINS(string, substring)

Tests whether a string contains a specified substring.

Syntax: CONTAINS(string, substring)

Examples:

CONTAINS([product_name], 'Pro')  -- Returns true if name contains 'Pro'
CONTAINS([email], '@gmail.com')  -- Check for Gmail addresses

Use Cases:

• Text filtering and categorization
• Email domain analysis
• Product categorization

FIND(string, substring)

Returns the position of a substring within a string (1-based indexing).

Syntax: FIND(string, substring)

Examples:

FIND([email], '@')     -- Position of @ symbol
FIND([description], 'error')  -- Find error mentions

Use Cases:

• Parse structured text
• Extract email domains
• Find specific patterns

LEFT(string, length)

Returns the leftmost characters from a string.

Syntax: LEFT(string, number_of_characters)

Examples:

LEFT([customer_id], 3)    -- First 3 characters
LEFT([product_code], 2)   -- Product category prefix
LEFT([phone], 3)          -- Area code

Use Cases:

• Extract prefixes and codes
• Categorization by prefix
• Data parsing

LOWER(string)

Converts a string to lowercase.

Syntax: LOWER(string)

Examples:

LOWER([customer_name])    -- Convert name to lowercase
LOWER([email])            -- Normalize email addresses

Use Cases:

• Data normalization
• Case-insensitive comparisons
• Email standardization

LTRIM(string)

Removes whitespace from the left side of a string.

Syntax: LTRIM(string)

Examples:

LTRIM([description])      -- Remove leading spaces
LTRIM([user_input])       -- Clean user input

Use Cases:

• Data cleaning
• Input sanitization
• Format standardization

PLUCK(string, delimiter, position)

Splits a string by a delimiter and returns the token at the specified position.

Syntax: PLUCK(string, delimiter, position)

Examples:

PLUCK([full_name], ' ', 1)     -- First name
PLUCK([full_name], ' ', 2)     -- Last name
PLUCK([file_path], '/', 3)     -- Third path segment
PLUCK([csv_data], ',', 2)      -- Second CSV column

Use Cases:

• Parse delimited data
• Extract name components
• File path analysis
• CSV data extraction

REPLACE(string, old_text, new_text)

Replaces all occurrences of a substring with another substring.

Syntax: REPLACE(string, old_substring, new_substring)

Examples:

REPLACE([phone], '-', '')         -- Remove dashes from phone
REPLACE([description], 'old', 'new')  -- Replace text
REPLACE([product_name], '&', 'and')   -- Replace ampersand

Use Cases:

• Data cleaning and standardization
• Text normalization
• Format conversion

RIGHT(string, length)

Returns the rightmost characters from a string.

Syntax: RIGHT(string, number_of_characters)

Examples:

RIGHT([order_id], 4)      -- Last 4 characters
RIGHT([file_name], 3)     -- File extension
RIGHT([account], 2)       -- Account suffix

Use Cases:

• Extract suffixes and extensions
• Account number analysis
• File type identification

RTRIM(string)

Removes whitespace from the right side of a string.

Syntax: RTRIM(string)

Examples:

RTRIM([description])      -- Remove trailing spaces
RTRIM([user_comment])     -- Clean user input

Use Cases:

• Data cleaning
• Input sanitization
• Format standardization

SUBSTR(string, start, length)

Extracts a substring from a string starting at a specified position.

Syntax: SUBSTR(string, start_position, length)

Examples:

SUBSTR([product_code], 2, 3)  -- Characters 2-4
SUBSTR([description], 1, 50)  -- First 50 characters
SUBSTR([date_string], 1, 4)   -- Extract year

Use Cases:

• Extract specific portions of text
• Parse structured data
• Truncate long descriptions

TRIM(string)

Removes whitespace from both sides of a string.

Syntax: TRIM(string)

Examples:

TRIM([customer_input])    -- Remove leading/trailing spaces
TRIM([description])       -- Clean description field

Use Cases:

• Data cleaning
• Input sanitization
• Consistent formatting

UPPER(string)

Converts a string to uppercase.

Syntax: UPPER(string)

Examples:

UPPER([product_code])     -- Standardize product codes
UPPER([state])            -- Standardize state abbreviations

Use Cases:

• Data standardization
• Code normalization
• Consistent display

Date Functions

DATEADD(datepart, number, date)

Adds a specified time interval to a date.

Syntax: DATEADD(datepart, number, date)

Date Parts: 'year', 'quarter', 'month', 'week', 'day', 'hour', 'minute', 'second'

Examples:

DATEADD('day', 30, [order_date])      -- 30 days after order
DATEADD('month', -1, TODAY())         -- One month ago
DATEADD('year', 1, [start_date])      -- One year later

Use Cases:

• Calculate future/past dates
• Subscription expiry dates
• Aging analysis
• Forecasting

DATEDIFF(datepart, start_date, end_date)

Calculates the difference between two dates.

Syntax: DATEDIFF(datepart, start_date, end_date)

Examples:

DATEDIFF('day', [order_date], [ship_date])    -- Days to ship
DATEDIFF('month', [hire_date], TODAY())       -- Months employed
DATEDIFF('year', [birth_date], TODAY())       -- Age in years

Use Cases:

• Calculate age and tenure
• Performance metrics (time to ship, resolve, etc.)
• Duration analysis

DATEPART(datepart, date)

Extracts a specific part from a date.

Syntax: DATEPART(datepart, date)

Examples:

DATEPART('year', [order_date])        -- Extract year
DATEPART('month', [transaction_date]) -- Extract month (1-12)
DATEPART('dow', [date])               -- Day of week (1-7)
DATEPART('quarter', [sales_date])     -- Quarter (1-4)

Use Cases:

• Group data by time periods
• Seasonal analysis
• Time-based reporting

DATETRUNC(datepart, date)

Truncates a date to the specified precision.

Syntax: DATETRUNC(datepart, date)

Examples:

DATETRUNC('month', [order_date])      -- First day of month
DATETRUNC('year', [transaction_date]) -- First day of year
DATETRUNC('week', [event_date])       -- First day of week

Use Cases:

• Group transactions by period
• Create time buckets
• Period-over-period analysis

NOW()

Returns the current date and time.

Syntax: NOW()

Examples:

NOW()                                 -- Current timestamp
DATEDIFF('hour', [created_at], NOW()) -- Hours since creation

Use Cases:

• Calculate current age or duration
• Real-time calculations
• Timestamp operations

TODAY()

Returns the current date (without time).

Syntax: TODAY()

Examples:

TODAY()                               -- Current date
DATEDIFF('day', [due_date], TODAY())  -- Days overdue

Use Cases:

• Calculate days until/since events
• Current date comparisons
• Aging reports

Conversion Functions

DATE(expression)

Converts an expression to a date format.

Syntax: DATE(expression)

Examples:

DATE([timestamp_field])              -- Extract date from timestamp
DATE('2023-12-25')                   -- Convert string to date

Use Cases:

• Extract date from datetime
• Convert string dates
• Date normalization

DATETIME(expression)

Converts an expression to a datetime format.

Syntax: DATETIME(expression)

Examples:

DATETIME([date_string])              -- Convert string to datetime
DATETIME([unix_timestamp])           -- Convert timestamp

Use Cases:

• Parse date strings
• Convert timestamps
• Datetime standardization

INT(expression)

Converts an expression to an integer.

Syntax: INT(expression)

Examples:

INT([decimal_field])                 -- Convert to integer
INT([string_number])                 -- Parse numeric string
INT([price] / 100)                   -- Convert cents to dollars

Use Cases:

• Remove decimal places
• Parse numeric strings
• Integer calculations

FLOAT(expression)

Converts an expression to a floating-point number.

Syntax: FLOAT(expression)

Examples:

FLOAT([integer_field])               -- Convert to decimal
FLOAT([percentage_string])           -- Parse percentage

Use Cases:

• Ensure decimal precision
• Parse numeric strings
• Mathematical calculations

Logical Functions

AND

Logical AND operation - returns true if both conditions are true.

Syntax: condition1 AND condition2

Examples:

[age] >= 18 AND [status] == 'active'          -- Adult and active
[revenue] > 1000 AND [region] == 'North'      -- High revenue in North
[date] >= '2023-01-01' AND [date] <= '2023-12-31'  -- Date range

Use Cases:

• Multiple condition filtering
• Complex business rules
• Data validation

OR

Logical OR operation - returns true if either condition is true.

Syntax: condition1 OR condition2

Examples:

[status] == 'pending' OR [status] == 'review'     -- Either status
[priority] == 'high' OR [value] > 10000           -- High priority or value
[region] == 'North' OR [region] == 'South'        -- Multiple regions

Use Cases:

• Alternative conditions
• Category grouping
• Flexible filtering

IF...THEN...ELSE...END

Conditional logic with comparison operators.

Syntax: IF condition THEN value1 ELSE value2 END

Examples:

IF [profit] > 0 THEN 'Profitable' ELSE 'Loss' END
IF [age] >= 65 THEN 'Senior' ELSE 'Regular' END
IF [score] >= 90 THEN 'A' ELSE IF [score] >= 80 THEN 'B' ELSE 'C' END

Use Cases:

• Conditional categorization
• Business rule implementation
• Data transformation

CASE...WHEN...THEN...ELSE...END

Conditional logic for equality comparisons.

Syntax: CASE field WHEN value THEN result ELSE alternative END

Examples:

CASE [status] WHEN 'active' THEN 1 ELSE 0 END
CASE [grade] WHEN 'A' THEN 4 WHEN 'B' THEN 3 WHEN 'C' THEN 2 ELSE 1 END
CASE [region] WHEN 'North' THEN 'N' WHEN 'South' THEN 'S' ELSE 'Other' END

Use Cases:

• Value mapping and translation
• Category scoring
• Status indicators

ISNULL(expression)

Tests if an expression is null.

Syntax: ISNULL(expression)

Examples:

ISNULL([optional_field])             -- Check if field is null
SUM(CASE WHEN ISNULL([value]) THEN 0 ELSE 1 END)  -- Count non-null values

Use Cases:

• Data quality checks
• Null value handling
• Conditional calculations

IFNULL(expression, alternative)

Returns the expression if not null, otherwise returns the alternative.

Syntax: IFNULL(expression, alternative_value)

Examples:

IFNULL([commission], 0)              -- Use 0 if commission is null
IFNULL([nickname], [full_name])      -- Use nickname or full name
IFNULL([discount], 0.0)              -- Default discount to 0

Use Cases:

• Provide default values
• Handle missing data
• Ensure calculations work with nulls

Aggregate Functions

AVG(expression)

Calculates the average of all values.

Syntax: AVG(field_or_expression)

Examples:

AVG([sales])                         -- Average sales
AVG([price] * [quantity])            -- Average order value
AVG(CASE [region] WHEN 'North' THEN [sales] ELSE NULL END)  -- Regional average

Use Cases:

• Performance benchmarking
• Central tendency analysis
• KPI calculations

COUNT(expression)

Counts non-null values.

Syntax: COUNT(field_or_expression)

Examples:

COUNT([order_id])                    -- Count of orders
COUNT(CASE [status] WHEN 'complete' THEN 1 ELSE NULL END)  -- Completed orders
COUNT(1)                             -- Count all rows

Use Cases:

• Record counting
• Completion rates
• Data availability analysis

COUNTD(expression)

Counts distinct (unique) values.

Syntax: COUNTD(field_or_expression)

Examples:

COUNTD([customer_id])                -- Unique customers
COUNTD([product_category])           -- Number of categories
COUNTD([region])                     -- Number of regions

Use Cases:

• Unique entity counting
• Diversity analysis
• Cardinality measurement

MAX(expression)

Returns the maximum value.

Syntax: MAX(field_or_expression)

Examples:

MAX([order_date])                    -- Most recent order
MAX([price])                         -- Highest price
MAX([score])                         -- Best score

Use Cases:

• Find peak values
• Latest dates
• Performance maximums

MEDIAN(expression)

Returns the median (middle) value.

Syntax: MEDIAN(field_or_expression)

Examples:

MEDIAN([salary])                     -- Middle salary
MEDIAN([response_time])              -- Typical response time
MEDIAN([age])                        -- Median age

Use Cases:

• Robust central tendency
• Outlier-resistant averages
• Typical value analysis

MIN(expression)

Returns the minimum value.

Syntax: MIN(field_or_expression)

Examples:

MIN([order_date])                    -- Earliest order
MIN([price])                         -- Lowest price
MIN([response_time])                 -- Fastest response

Use Cases:

• Find minimum values
• Earliest dates
• Performance minimums

MODE(expression)

Returns the most frequently occurring value.

Syntax: MODE(field_or_expression)

Examples:

MODE([product_category])             -- Most common category
MODE([customer_segment])             -- Most frequent segment
MODE([day_of_week])                  -- Most common day

Use Cases:

• Most popular items
• Typical behavior patterns
• Frequency analysis

PERCENTILE_N(expression)

Returns the Nth percentile of values.

Available Functions: PERCENTILE_1, PERCENTILE_5, PERCENTILE_25, PERCENTILE_75, PERCENTILE_95, PERCENTILE_99

Examples:

PERCENTILE_25([income])              -- 25th percentile (Q1)
PERCENTILE_75([income])              -- 75th percentile (Q3)
PERCENTILE_95([response_time])       -- 95th percentile
PERCENTILE_99([transaction_value])   -- 99th percentile

Use Cases:

• Quartile analysis
• Performance SLAs
• Outlier identification
• Distribution analysis

STDEV(expression)

Calculates the sample standard deviation.

Syntax: STDEV(field_or_expression)

Examples:

STDEV([sales])                       -- Sales variability
STDEV([response_time])               -- Response time consistency
STDEV([scores])                      -- Score distribution

Use Cases:

• Measure variability
• Quality control
• Risk assessment

STDEVP(expression)

Calculates the population standard deviation.

Syntax: STDEVP(field_or_expression)

Examples:

STDEVP([test_scores])                -- Population standard deviation
STDEVP([measurements])               -- Full population variability

Use Cases:

• Complete population analysis
• Quality metrics
• Statistical analysis

SUM(expression)

Calculates the sum of all values.

Syntax: SUM(field_or_expression)

Examples:

SUM([sales])                         -- Total sales
SUM([quantity] * [price])            -- Total revenue
SUM(CASE [status] WHEN 'complete' THEN 1 ELSE 0 END)  -- Count completed

Use Cases:

• Total calculations
• Revenue summation
• Conditional counting

VAR(expression)

Calculates the sample variance.

Syntax: VAR(field_or_expression)

Examples:

VAR([returns])                       -- Return variability
VAR([performance])                   -- Performance variance

Use Cases:

• Measure dispersion
• Risk analysis
• Consistency metrics

VARP(expression)

Calculates the population variance.

Syntax: VARP(field_or_expression)

Examples:

VARP([population_data])              -- Population variance
VARP([complete_dataset])             -- Full dataset variance

Use Cases:

• Complete population analysis
• Statistical calculations
• Quality measurement

Analytical Functions

FIRST()

Returns the number of rows from the current row to the first row in the partition.

Syntax: FIRST()

Examples:

FIRST()                              -- Distance to first row
[sales] - LOOKUP([sales], FIRST())   -- Difference from first value

Use Cases:

• Relative positioning
• Comparison to baseline
• Sequence analysis

INDEX()

Returns the index (position) of the current row.

Syntax: INDEX()

Examples:

INDEX()                              -- Row number (1-based)
INDEX() / COUNT([id])                -- Relative position as percentage

Use Cases:

• Row numbering
• Position-based calculations
• Ranking analysis

LAST()

Returns the number of rows from the current row to the last row in the partition.

Syntax: LAST()

Examples:

LAST()                               -- Distance to last row
[sales] - LOOKUP([sales], LAST())    -- Difference from last value

Use Cases:

• End-of-period comparisons
• Relative positioning
• Sequence analysis

LOOKUP(expression, offset)

Returns the value of an expression at a specified row offset.

Syntax: LOOKUP(expression, offset)

Examples:

LOOKUP([sales], -1)                  -- Previous row's sales
LOOKUP([price], 1)                   -- Next row's price
LOOKUP([value], 0)                   -- Current row's value
[sales] - LOOKUP([sales], -1)        -- Change from previous period

Use Cases:

• Period-over-period comparisons
• Time series analysis
• Trend calculations

NTILE(expression, tiles)

Distributes rows into a specified number of groups (tiles).

Syntax: NTILE(expression, number_of_tiles)

Examples:

NTILE([sales], 4)                    -- Quartiles (1-4)
NTILE([performance], 10)             -- Deciles (1-10)
NTILE([score], 5)                    -- Quintiles (1-5)

Use Cases:

• Performance tiers
• Customer segmentation
• Percentile buckets

RANK(expression)

Returns the rank of each row with gaps for tied values.

Syntax: RANK(expression)

Examples:

RANK([sales])                        -- Sales ranking with gaps
RANK([score])                        -- Score ranking
21 - RANK([score])                   -- Reverse ranking

Use Cases:

• Performance ranking
• Leaderboards
• Competitive analysis

RANK_DENSE(expression)

Returns the rank of each row without gaps for tied values.

Syntax: RANK_DENSE(expression)

Examples:

RANK_DENSE([revenue])                -- Dense revenue ranking
RANK_DENSE([performance])            -- Performance ranking without gaps

Use Cases:

• Continuous ranking
• Performance tiers
• Grade assignments

RUNNING_AVG(expression)

Calculates a running average from the first row to the current row.

Syntax: RUNNING_AVG(expression)

Examples:

RUNNING_AVG([daily_sales])           -- Cumulative average sales
RUNNING_AVG([response_time])         -- Running average response time

Use Cases:

• Cumulative performance tracking
• Trend smoothing
• Progressive averages

RUNNING_COUNT(expression)

Calculates a running count from the first row to the current row.

Syntax: RUNNING_COUNT(expression)

Examples:

RUNNING_COUNT([order_id])            -- Cumulative order count
RUNNING_COUNT([customer])            -- Running customer count

Use Cases:

• Cumulative counters
• Progress tracking
• Sequential numbering

RUNNING_SUM(expression)

Calculates a running sum from the first row to the current row.

Syntax: RUNNING_SUM(expression)

Examples:

RUNNING_SUM([daily_revenue])         -- Cumulative revenue
RUNNING_SUM([units_sold])            -- Running total units
RUNNING_SUM([costs])                 -- Cumulative costs

Use Cases:

• Cumulative totals
• Year-to-date calculations
• Progressive summation

TOTAL(expression)

Calculates the total for the entire partition.

Syntax: TOTAL(expression)

Examples:

[sales] / TOTAL([sales])             -- Percentage of total
TOTAL([revenue])                     -- Total revenue for partition

Use Cases:

• Percentage calculations
• Proportion analysis
• Total comparisons

WINDOW_AVG(expression, start, end)

Calculates an average over a moving window of rows.

Syntax: WINDOW_AVG(expression, start_offset, end_offset)

Examples:

WINDOW_AVG([sales], -2, 2)           -- 5-day moving average
WINDOW_AVG([price], -6, 0)           -- 7-period trailing average
WINDOW_AVG([performance], -1, 1)     -- 3-period centered average

Use Cases:

• Moving averages
• Trend smoothing
• Seasonal adjustments

WINDOW_COUNT(expression, start, end)

Counts values over a moving window of rows.

Syntax: WINDOW_COUNT(expression, start_offset, end_offset)

Examples:

WINDOW_COUNT([orders], -6, 0)        -- Orders in last 7 periods
WINDOW_COUNT([events], -1, 1)        -- Events in 3-period window

Use Cases:

• Moving counts
• Activity windows
• Event frequency

WINDOW_SUM(expression, start, end)

Calculates a sum over a moving window of rows.

Syntax: WINDOW_SUM(expression, start_offset, end_offset)

Examples:

WINDOW_SUM([sales], -6, 0)           -- 7-day rolling sum
WINDOW_SUM([revenue], -3, 3)         -- 7-period centered sum
WINDOW_SUM([quantity], -11, 0)       -- 12-month rolling total

Use Cases:

• Rolling totals
• Moving sums
• Trend analysis

Examples and Use Cases

Business Metrics

Customer Segmentation

-- RFM Score calculation
CASE 
  WHEN [recency_days] <= 30 AND [frequency] >= 5 AND [monetary] >= 1000 THEN 'Champions'
  WHEN [recency_days] <= 60 AND [frequency] >= 3 AND [monetary] >= 500 THEN 'Loyal'
  WHEN [recency_days] <= 90 AND [frequency] >= 2 THEN 'Potential'
  ELSE 'At Risk'
END

Profit Margin Analysis

-- Calculate profit margin percentage
ROUND(([revenue] - [cost]) / [revenue] * 100, 2)

-- Categorize profit levels
CASE 
  WHEN ([revenue] - [cost]) / [revenue] > 0.30 THEN 'High Margin'
  WHEN ([revenue] - [cost]) / [revenue] > 0.15 THEN 'Medium Margin'
  WHEN ([revenue] - [cost]) / [revenue] > 0 THEN 'Low Margin'
  ELSE 'Loss'
END

Sales Performance

-- Sales growth rate
([current_sales] - [previous_sales]) / [previous_sales] * 100

-- Quota attainment
[actual_sales] / [quota] * 100

-- Sales rank within team
RANK([total_sales])

Time-Based Analysis

Age Calculations

-- Customer age
DATEDIFF('year', [birth_date], TODAY())

-- Account tenure in months
DATEDIFF('month', [account_created], TODAY())

-- Days since last purchase
DATEDIFF('day', [last_purchase_date], TODAY())

Seasonal Analysis

-- Quarter of year
DATEPART('quarter', [transaction_date])

-- Month name
CASE DATEPART('month', [date])
  WHEN 1 THEN 'January'
  WHEN 2 THEN 'February'
  WHEN 3 THEN 'March'
  -- ... continue for all months
  ELSE 'Unknown'
END

-- Is weekend?
CASE DATEPART('dow', [date])
  WHEN 1 THEN 'Weekend'  -- Sunday
  WHEN 7 THEN 'Weekend'  -- Saturday
  ELSE 'Weekday'
END

Data Quality and Cleaning

Email Validation

-- Valid email format check
CASE 
  WHEN CONTAINS([email], '@') AND CONTAINS([email], '.') THEN 'Valid'
  ELSE 'Invalid'
END

-- Extract domain
RIGHT([email], LEN([email]) - FIND([email], '@'))

Text Cleaning

-- Clean phone numbers
REPLACE(REPLACE(REPLACE([phone], '(', ''), ')', ''), '-', '')

-- Standardize names
TRIM(UPPER([last_name])) + ', ' + TRIM([first_name])

-- Extract initials
LEFT([first_name], 1) + LEFT([last_name], 1)

Advanced Calculations

Statistical Analysis

-- Z-score calculation
([value] - AVG([value])) / STDEV([value])

-- Coefficient of variation
STDEV([sales]) / AVG([sales]) * 100

-- Percentile rank
RANK([score]) / COUNT([score]) * 100

Financial Calculations

-- Return on investment
([ending_value] - [beginning_value]) / [beginning_value] * 100

-- Compound annual growth rate
POWER([ending_value] / [beginning_value], 1.0 / [years]) - 1

-- Present value
[future_value] / POWER(1 + [interest_rate], [periods])

Conditional Aggregations

Complex Counts

-- Count of high-value customers
SUM(CASE WHEN [total_purchases] > 10000 THEN 1 ELSE 0 END)

-- Percentage of completed orders
SUM(CASE [status] WHEN 'completed' THEN 1 ELSE 0 END) / COUNT([order_id]) * 100

-- Average age of active customers
AVG(CASE [status] WHEN 'active' THEN [age] ELSE NULL END)

Revenue Analysis

-- Revenue from new customers
SUM(CASE WHEN [customer_type] = 'new' THEN [revenue] ELSE 0 END)

-- Percentage of revenue from top product
MAX([product_revenue]) / SUM([product_revenue]) * 100

-- Average order value by region
AVG(CASE [region] WHEN 'North' THEN [order_value] ELSE NULL END)

Best Practices

Formula Design

1. Use Clear Field References

   • Always use square brackets: [field_name]
   • Use descriptive field names
   • Avoid special characters in field names

2. Handle Null Values

   • Use IFNULL() or ZN() for default values
   • Consider null behavior in calculations
   • Test formulas with missing data

3. Optimize Performance

   • Use simple functions when possible
   • Avoid unnecessary string operations
   • Consider data types in calculations

Readability and Maintenance

1. Format Complex Formulas

   -- Good: Multi-line with indentation
   CASE [customer_tier]
    WHEN 'Gold' THEN [base_discount] * 1.5
    WHEN 'Silver' THEN [base_discount] * 1.2
    ELSE [base_discount]
   END
   
   -- Avoid: Single long line
   CASE [customer_tier] WHEN 'Gold' THEN [base_discount] * 1.5 WHEN 'Silver' THEN [base_discount] * 1.2 ELSE [base_discount] END

2. Use Meaningful Names

   • Choose descriptive calculated field names
   • Avoid abbreviations unless commonly understood
   • Include units in names when relevant (e.g., "Days_Since_Purchase")

3. Document Complex Logic

   • Add comments in field descriptions
   • Document business rules
   • Explain calculation methodology

Error Prevention

1. Test with Sample Data

   • Verify calculations with known results
   • Test edge cases (zeros, nulls, extremes)
   • Validate against business requirements

2. Use Appropriate Data Types

   • Ensure numeric fields for mathematical operations
   • Convert strings to numbers when needed
   • Handle date formats consistently

3. Validate Business Logic

   • Confirm formulas match business rules
   • Test with stakeholders
   • Document assumptions

Performance Considerations

1. Minimize Complex Calculations

   • Pre-calculate values when possible
   • Use simpler alternatives when available
   • Consider database performance

2. Efficient Aggregations

   • Use appropriate aggregate functions
   • Minimize nested calculations
   • Consider data volume impact

3. String Operations

   • Use string functions efficiently
   • Avoid unnecessary case conversions
   • Consider regex for complex patterns

Troubleshooting

Common Errors

Syntax Errors

-- Error: Missing square brackets
revenue - cost  -- Should be: [revenue] - [cost]

-- Error: Unmatched parentheses
SUM([sales] * 1.1  -- Should be: SUM([sales] * 1.1)

-- Error: Invalid quotes
[status] == "active"  -- Should be: [status] == 'active'

Type Conversion Issues

-- Error: String concatenation with numbers
[name] + [age]  -- Should be: [name] + ' (' + INT([age]) + ')'

-- Error: Date arithmetic with strings
[date] + 30  -- Should be: DATEADD('day', 30, [date])

Null Value Problems

-- Error: Null propagation
[value1] + [value2]  -- If either is null, result is null
-- Solution: IFNULL([value1], 0) + IFNULL([value2], 0)

-- Error: Division by zero
[numerator] / [denominator]  -- May cause error if denominator is 0
-- Solution: CASE WHEN [denominator] != 0 THEN [numerator] / [denominator] ELSE 0 END

Debugging Techniques

1. Simplify Complex Formulas

   • Break into smaller parts
   • Test each component separately
   • Build complexity gradually

2. Use Sample Data

   • Test with known values
   • Verify intermediate results
   • Check edge cases

3. Validate Data Types

   • Ensure proper field types
   • Convert when necessary
   • Handle null values appropriately

Performance Issues

1. Slow Calculations

   • Simplify complex nested functions
   • Pre-calculate repeated expressions
   • Consider alternative approaches

2. Memory Problems

   • Limit string operations
   • Reduce data set size if possible
   • Optimize aggregation logic

3. Timeout Errors

   • Break into smaller calculations
   • Optimize formula logic
   • Consider data preprocessing

Getting Help

When troubleshooting calculated fields:

1. Check Syntax First

   • Verify bracket placement
   • Confirm quote matching
   • Validate function names

2. Test with Simple Data

   • Use minimal test cases
   • Verify expected behavior
   • Isolate problem areas

3. Review Documentation

   • Confirm function syntax
   • Check parameter requirements
   • Verify data type compatibility

4. Use Error Messages

   • Read error details carefully
   • Identify specific problem areas
   • Apply targeted fixes

Remember: The formula engine provides real-time validation and preview functionality to help catch errors early in the development process.