Functions and operators

Use functions and operators when a formula needs math, aggregation, date logic, comparisons, or conditional behavior. For formula reference syntax, start with formula basics and formula syntax.

Operators

Operator	Usage	Description	Things to keep in mind
`+`	`value1 + value2`	Adds two values.
`-`	`value1 - value2`	Subtracts the second value from the first.
`*`	`value1 * value2`	Multiplies two values.
`/`	`value1 / value2`	Divides the first value by the second.
`%`	`value1 % value2`	Returns the remainder when `value1` is divided by `value2`.
`^`	`base ^ exponent`	Raises `base` to `exponent`.
`==`	`value1 == value2`	Checks whether two values are equal.	Date comparisons work. For example, `if(thisMonth() == '2025-01-01', 1, 0)` returns 1 in January 2025.
`!=`	`value1 != value2`	Checks whether two values are not equal.	Date comparisons work. For example, `if(thisMonth() != '2025-01-01', 1, 0)` returns 1 outside January 2025.
`>`	`value1 > value2`	Checks whether the first value is greater than the second.	Date comparisons work. For example, `if(thisMonth() > '2025-01-01', 1, 0)` returns 1 after January 2025.
`<`	`value1 < value2`	Checks whether the first value is less than the second.	Date comparisons work. For example, `if(thisMonth() < '2025-01-01', 1, 0)` returns 1 before January 2025.
`>=`	`value1 >= value2`	Checks whether the first value is greater than or equal to the second.	Date comparisons work. For example, `if(thisMonth() >= '2025-01-01', 1, 0)` returns 1 in or after January 2025.
`<=`	`value1 <= value2`	Checks whether the first value is less than or equal to the second.	Date comparisons work. For example, `if(thisMonth() <= '2025-01-01', 1, 0)` returns 1 in or before January 2025.
`(` and `)`	`(condition1 AND condition2)`	Groups calculations or logical expressions.

Aggregation

Function	Signature	Description	Example	Things to keep in mind
`sum()`	`sum(reference)`	Returns the sum of a database field or dimensional driver reference.	`sum(Employees.Salary)`
`sumProduct()`	`sumProduct(reference1, reference2)`	Calculates the sum of the products of corresponding entries in two database field or dimensional driver reference.	`sumProduct(Deals.Quantity, Deals.UnitPrice)`	Both references must have the same number of entries and corresponding dimensional segments.
`count()`	`count(reference)`	Returns the number of values in a database field or dimensional driver reference.	`count(Employees.Name)`
`avg()`	`avg(reference)`	Returns the numerical average value in a database field or dimensional driver reference.	`avg(Employees.Salary)`
`min()`	`min(reference...)`	Returns the numerical minimum value in a list of values.	`min(Deals.ContractValue)`
`max()`	`max(reference...)`	Returns the numerical maximum value in a list of values.	`max(Deals.ContractValue)`
`first()`	`first(reference...)`	Returns the first value in a list of values.	`first(HeadcountByDepartment)`
`firstValue()`	`firstValue(reference...)`	Returns the first non-null value in a list of values.	`firstValue(RevenueByRegion)`
`last()`	`last(reference...)`	Returns the last value in a list of values.	`last(RevenueByRegion)`
`lastValue()`	`lastValue(reference...)`	Returns the last non-null value in a list of values.	`lastValue(RevenueByRegion)`
`single()`	`single(reference...)`	Checks to see if its list of references returns a single value. Returns the value if true, otherwise returns an ASSERTION error.	`single(Employees.Department)`

Math & rounding

Function	Signature	Description	Things to keep in mind
`round()`	`round(value, [places])`	Rounds a number to a certain number of decimal places according to standard rules. Maximum precision is 5 decimal places.	Coarser rounding than the ones place is not supported; do not use negative values for `[places]`.
`roundDown()`	`roundDown(value, [places])`	Rounds down a number to a specified number of decimal places. If places is omitted, rounds down to the nearest integer. Maximum precision is 5 decimal places.
`roundUp()`	`roundUp(value, [places])`	Rounds up a number to a specified number of decimal places. If places is omitted, rounds up to the nearest integer. Maximum precision is 5 decimal places.	`-roundDown(-value)` produces the same round-up behavior and can help when reading older formulas.
`floor()`	`floor(value, [factor])`	Rounds down a number to the nearest multiple of a factor. If factor is omitted, rounds down to the nearest integer.
`ceiling()`	`ceiling(value, [factor])`	Rounds up a number to the nearest multiple of a factor. If factor is omitted, rounds up to the nearest integer.
`power()`	`power(base, exponent)`	Returns the value of base raised to the power of exponent. Aliases: pow(base, exponent); base^exponent.
`exp()`	`exp(value)`	Returns the value of e raised to the power of value.
`log()`	`log(argument, [base])`	Returns the logarithm of argument with a given base. If base is not provided, it defaults to 10.
`ln()`	`ln(argument)`	Returns the natural logarithm of argument.

Date calculation

Function	Signature	Description	Things to keep in mind
`dateDiff()`	`dateDiff(start_date, end_date, unit)`	Returns the number of whole “units” between the start_date and end_date. Valid units are “d”, “m”, “w”, “q”, “y” (days, months, weeks, quarters, years).	If the result is negative, consider flipping `start_date` and `end_date`.
`dateAdd()`	`dateAdd(date, quantity, unit)`	Returns the sum of the date plus the quantity in units. Valid units are “d”, “m”, “w”, “q”, “y” (days, months, weeks, quarters, years).	This returns a date. Use a Date driver when the formula result should be a date.
`dateSub()`	`dateSub(date, quantity, unit)`	Returns the sum of the date minus the quantity in units. Valid units are “d”, “m”, “w”, “q”, “y” (days, months, weeks, quarters, years).	This returns a date. Use a Date driver when the formula result should be a date.
`netWorkDays()`	`netWorkDays(start_date, end_date, [weekends], [holidays])`	Returns the number of net work days between start_date (inclusive) and end_date (exclusive) excluding holidays and weekends. The optional weekend parameter expects a string of 7 characters, each representing a day of the week. 1 means the day is a weekend, 0 means it is a work day. The first character represents Monday (default is ‘0000011’). The holiday optional parameter allows you to specify a list of holidays as a string of comma separated dates in the format YYYY-MM-DD (default is empty).

Date access

Function	Signature	Description
`startOfMonth()`	`startOfMonth(date_expression)`	Returns the first day of the month for the date expression.
`endOfMonth()`	`endOfMonth(date_expression)`	Returns the last day of the month for the date expression.
`thisMonth()`	`thisMonth()`	Returns the first day of the month being evaluated.
`lastMonth()`	`lastMonth()`	Returns the first day of the previous month being evaluated.
`thisQuarter()`	`thisQuarter()`	Returns the first day of the quarter being evaluated.
`lastQuarter()`	`lastQuarter()`	Returns the first day of the previous quarter being evaluated.
`thisYear()`	`thisYear()`	Returns the first day of the year being evaluated.
`lastYear()`	`lastYear()`	Returns the first day of the previous year being evaluated.
`daysInMonth()`	`daysInMonth()`	Returns the number of days in the month being evaluated.
`year()`	`year(date_expression)`	Returns the year of the date expression. Example: year(‘2025-01-01’) returns 2025.
`quarter()`	`quarter(date_expression)`	Returns the quarter of the date expression. Example: quarter(‘2025-04-24’) returns 2.
`month()`	`month(date_expression)`	Returns the month of the date expression. Example: month(‘2025-03-01’) returns 3.
`weeknum()`	`weeknum(date_expression)`	Returns the number of weeks from the start of the year of the date expression. Example: weeknum(‘2025-03-01’) returns 9.
`day()`	`day(date_expression)`	Returns the day of the month of the date expression.

Logic & error handling

Function	Signature	Description	Things to keep in mind
`if()`	`IF(logical_expression, value_if_true, value_if_false)`	Returns one value if a logical expression is TRUE and another if it is FALSE.	The condition must be a logical expression, such as `1 == 1`. Values can be nested formula expressions.
`ifError()`	`ifError(value, value_if_error)`	Returns value if it does not evaluate to an error and value_if_error otherwise. The following error types are handled: NULL, DIV/0	This does not suppress circular-reference (`CIRC`) errors.
`coalesce()`	`coalesce(val1, val2, ..., val_n)`	Returns the first non-null value.	Filtered pills that return zero results are treated as null. This is useful for switch-style logic.
`and()`	`and(expression1, expression2, ...)`	Returns true if all expressions are true/non-null/non-zero, otherwise returns false.	Use parentheses to make nested logic easier to read.
`or()`	`or(expression1, expression2, ...)`	Returns true if any expression is true/non-null/non-zero, otherwise returns false.	Use parentheses to make nested logic easier to read.

Time-unit conversion

No public time-unit conversion functions are exposed in the formula autocomplete registry.

Special keywords

Item	Where it appears	Description	Things to keep in mind
`NULL`	Values section	Represents the NULL value.
`this.` / This Segment	This Segment and This Database autocomplete sections	References the current segment or database row context when matching dimensions and database fields.	Use this for formulas that should adapt across segments instead of hardcoding each filter.
`lastClose()`	Function autocomplete	Returns the last closed month.	Use it when a formula should anchor to the last closed month rather than the month currently being evaluated.
Relative date references	Date filters on formula references	Includes This month, Last month, Last close, One year ago, Quarter-to-date, Year-to-date, and rolling actuals ranges.	References with multi-month ranges usually need an aggregation function such as `sum()`, `avg()`, or `max()`.

​Operators

​Aggregation

​Math & rounding

​Date calculation

​Date access

​Logic & error handling

​Time-unit conversion

​Special keywords

​What’s next