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() |
Period-based
Period-based functions are used for stock-type measures. They work with the period that the current filter context sets for a date dimension.
| Function | Description | Example |
|---|---|---|
PERIODDAYS([dimension]) |
Number of calendar days in the current period, taken from the specified date dimension | PERIODDAYS([Date]) |
ACTIVEDAYS([field], [dimension]) |
Number of days in the current period on which the field has a non-null value | ACTIVEDAYS([Stock], [Date]) |
OPENINGVALUE([dimension]) |
Filter that selects the opening value of the period — the latest date strictly before the period start | SUM({OPENINGVALUE([Date])} [Stock]) |
CLOSINGVALUE([dimension]) |
Filter that selects the closing value of the period — the latest date within the period | SUM({CLOSINGVALUE([Date])} [Stock]) |
Rules for period-based functions:
- Arguments are written only as references in square brackets — literals and expressions are not accepted
- The
[dimension]argument must be a dimension of type Date or DateTime OPENINGVALUEandCLOSINGVALUEare used only inside a filter in curly braces, in front of the field being aggregated- If the current context defines no period boundaries for the dimension, the editor shows a warning
- A formula can contain at most 20 period-based functions
- In the formula builder,
PERIODDAYSandACTIVEDAYSare in the Date & Time section of the elements panel, andOPENINGVALUEandCLOSINGVALUEare in the Range section
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])
Period boundaries:
SUM({OPENINGVALUE([Date])} [Stock])
SUM({CLOSINGVALUE([Date])} [Stock])
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 (
\)