Formula syntax
Data types
| Type | Format | Examples |
|---|---|---|
| Numeric | Integers and decimals | 10, 3.14, -5 |
| String | Enclosed in single quotes | 'Hello', 'Data' |
| Date | 'YYYY-MM-DD' |
'2023-11-23' |
| DateTime | 'YYYY-MM-DD HH:MM:SS' |
'2023-11-23 14:30:00' |
| Boolean | TRUE or FALSE (case-insensitive) |
TRUE, false |
| NULL | NULL (case-insensitive) |
NULL |
Identifiers
- Field references:
[Field Name](e.g.,[Sales],[Customer Name]) - Can reference measures calculated from other measures/dimensions
Operators
Arithmetic
+, -, *, /
Comparison
=, !=, <>, >, <, >=, <=, LIKE, NOT LIKE, IN, NOT IN, BETWEEN, NOT BETWEEN
Wildcards for LIKE:
- % - matches zero or more characters
- _ - matches exactly one character
Examples:
[Status] LIKE 'Active%'
[ID] IN (1, 2, 3)
[Price] BETWEEN (10, 100)
NULL testing
IS NULL, IS NOT NULL
Logical
AND, OR, NOT
String concatenation
& (e.g., 'Hello' & 'World' → 'HelloWorld')
Escape character
\ (e.g., 'O\'Reilly')
Functions
Aggregation
| Function | Description | Example |
|---|---|---|
SUM([field]) |
Sum of values | SUM([Revenue]) |
AVG([field]) |
Average of values | AVG([Temperature]) |
COUNT([field]) |
Count of non-null values | COUNT([Orders]) |
MIN([field]) |
Minimum value | MIN([Price]) |
MAX([field]) |
Maximum value | MAX([Score]) |
COUNTDISTINCT([field]) |
Count of distinct values | COUNTDISTINCT([CustomerID]) |
String
| Function | Description | Example |
|---|---|---|
LEFT([field], length) |
Leftmost characters | LEFT([Name], 3) |
RIGHT([field], length) |
Rightmost characters | RIGHT([Code], 2) |
LENGTH([field]) |
String length | LENGTH([Description]) |
Date/DateTime
| Function | Description | Example |
|---|---|---|
YEAR([field]) |
Extract year | YEAR([OrderDate]) |
MONTH([field]) |
Extract month (1-12) | MONTH([BirthDate]) |
DAY([field]) |
Extract day (1-31) | DAY([DueDate]) |
HOUR([field]) |
Extract hour (0-23) | HOUR([Timestamp]) |
MINUTE([field]) |
Extract minute (0-59) | MINUTE([LogTime]) |
SECOND([field]) |
Extract second (0-59) | SECOND([EventTime]) |
DATE(year, month, day) |
Create date | DATE(2023, 12, 25) |
DATETIME(...) |
Create datetime | DATETIME(2023, 12, 25, 14, 30, 0) |
DATE_TRUNC(unit, [field]) |
Truncate datetime to a specified precision | DATE_TRUNC('month', '2023-11-23 14:30:45') |
TODAY() |
Get current date in 'YYYY-MM-DD' format | TODAY() |
Conditional
| Function | Description | Example |
|---|---|---|
IF(condition, true, false) |
Conditional logic | IF([Qty] > 10, 'Bulk', 'Single') |
NULL handling
| Function | Description | Example |
|---|---|---|
ISNULL([field]) |
Check for NULL | ISNULL([MiddleName]) |
NULLIF([field], value) |
Return NULL if equal | NULLIF([Status], 'Pending') |
Set analysis (Filtering)
Enclosed in curly braces {}:
Basic syntax:
{[Region] = 'North'}
{[Status] IN ('Active', 'Pending')}
{[Age] BETWEEN (18, 65)}
Wildcard matching:
{[Product] LIKE 'Pro%'}
Combined with fields/aggregation:
({[Region] = 'North'} [Sales])
SUM({[Year] = 2023} [Revenue])
Complex conditions:
{[Department] = 'Sales' AND ([Status] = 'Active' OR [HireDate] > '2020-01-01')}
Expressions
- Use parentheses
()for grouping - Functions can be nested
- Examples:
sql SUM([Sales]) / COUNTDISTINCT([CustomerID]) IF([Score] > 90, 'A', IF([Score] > 80, 'B', 'C')) LEFT([Name], 1) & '. ' & [LastName]
Notes
- Field names are case-sensitive
- String comparisons are case-sensitive by default
- Date/DateTime literals must use exact format shown
- NULL values must be compared using
IS NULL/IS NOT NULL - Escape special characters in strings with backslash (
\)