Formulas
Functions & operators
Formulas
Functions & operators
This guide provides a comprehensive overview of all available functions and operators in the system.
Basic operators
| Operator | What it does | Usage | Things to keep in mind |
|---|---|---|---|
| + | Addition | value1 + value2 | |
| - | Subtraction | value1 - value2 | |
| * | Multiplication | value1 * value2 | |
| / | Division | value1 / value2 | |
| ^ | Exponentiation | base ^ exponent | |
| == | Equal to | value1 == value2 | |
| != | Not equal to | value1 != value2 | |
| > | Greater than | value1 > value2 | |
| < | Less than | value1 < value2 | |
| >= | Greater than or equal to | value1 >= value2 | |
| <= | Less than or equal to | value1 <= value2 | |
| AND | Logical AND | condition1 AND condition2 | You can use parentheses to create nested or more complex logic. |
| OR | Logical OR | condition1 OR condition2 | You can use parentheses to create nested or more complex logic. |
| NULL | Null value | NULL |
Logical functions
| Function | What it does | How to use it | Things to keep in mind |
|---|---|---|---|
| IF(condition, value_if_true, value_if_false) | Checks if something is true and returns one value if it is, and another if it isn’t. | • condition (required): A logical expression that returns TRUE or FALSE (e.g., 1 == 1).• value_if_true (required): The result if the condition is true. • value_if_false (required): The result if the condition is false. | The condition must return TRUE or FALSE. A non-null value does not evaluate to TRUE, e.g. you must use \ if(1==1,…), not just if(1,…).Values can themselves be entire formula expressions, allowing you to nest logic as deeply as you need. |
| ifError(value, fallback) | Returns the value unless it’s an error—then returns the fallback. | • value (required): What you want to check for error. • fallback (required): What to return if value is an error. | Doesn’t suppress CIRC errors (circular references), since those usually signal a deeper modeling issue. |
| coalesce(val1, val2, …, val_n) | Returns the first value that isn’t null. | val1, val2, …, val_n (2 or more required): Returns the first non-null value encountered. | If any expression passed as val is a filtered pill that returns zero results, coalesce will treat it as null. This is commonly used for logic like “switch” statements. |
Rounding functions
| Function | What it does | How to use it | Things to keep in mind |
|---|---|---|---|
| round(value, [places]) | Rounds a number to the nearest value based on standard rounding rules. | • value (required): The number to round. • places (optional): How many decimal places to keep. Defaults to 0. | Coarser rounding than to the ones place is not supported e.g. with negative values for [places]. |
| roundDown(value, [places]) | Always rounds a number down. | • value (required): The number to round. • places (optional): How many decimal places to keep. Defaults to 0. | roundUp is not natively supported, but you can implement it with -roundDown(-value, places) |
Aggregation functions
| Function | What it does | How to use it | Things to keep in mind |
|---|---|---|---|
| sum(reference[, reference2]) | Adds up values from a database column, dimensional driver, driver group, or set of individual drivers. | • reference (required): The values to sum. • reference2 (optional): You can provide additional values. | |
| avg(reference) | Returns the numerical average of value in a database column, dimensional driver, or driver group. | reference (required): The reference to average. | |
| min(reference[, reference2…]) | Returns the smallest value from a list. | reference (required): One or more values to find the minimum of. | |
| max(reference[, reference2…]) | Returns the largest value from a list. | reference (required): One or more values to find the maximum of. | |
| count(reference) | Returns the number of values in a database column, dimensional driver, or driver group. | reference (required): The reference to count. | |
| first(reference[, reference2…]) | Returns the first value in a list. | reference (required): One or more values to return the first from. | |
| single(reference[, reference2…]) | Checks to see if its list of references returns a single value in each month. Returns the value if true, otherwise returns an error. | reference (required): One or more values to check. | |
| sumProduct(reference1, reference2) | Calculates the sum of the products of corresponding entries in two database field or dimensional driver references. | reference1, reference2 (required): The two sets of values to multiply + sum | Both references must have the same number of entries in them, and also have corresponding dimensional segments. |
Date functions
If you’re getting blank values, please ensure that the drivers you’re working with have their type set to DATE.
| Function | What it does | How to use it |
|---|---|---|
| startOfMonth(date_expression) | Returns the first day of the month for the date expression. | date_expression (required): The date to find the start of month for. |
| endOfMonth(date_expression) | Returns the last day of the month for the date expression. | date_expression (required): The date to find the end of month for. |
| thisMonth() | Returns the first day of the month being evaluated. | No input expected. |
| lastMonth() | Returns the first day of the previous month being evaluated. | No input expected. |
| thisQuarter() | Returns the first day of the quarter being evaluated. | No input expected. |
| lastQuarter() | Returns the first day of the previous quarter being evaluated. | No input expected. |
| thisYear() | Returns the first day of the year being evaluated. | No input expected. |
| lastYear() | Returns the first day of the previous year being evaluated. | No input expected. |
| lastClose() | Returns the first day of the month of last close. | No input expected. |
| daysInMonth() | Returns the number of days in the month being evaluated. | No input expected. |
Date calculation functions
If you’re getting blank values, please ensure that the drivers you’re working with have their type set to the appropriate output type (Number or Date).
| Function | What it does | How to use it | Things to keep in mind |
|---|---|---|---|
| dateDiff(start_date, end_date, unit) | Returns the number of whole “units” between the start_date and end_date. | • start_date (required): The start date. • end_date (required): Required. The end date. • unit (required): The unit of time to measure. Valid units are ‘d’, ‘w’, ‘m’, ‘q’, and ‘y’ | If you are getting a negative value, you should consider flipping start_date and end_date |
| dateAdd(date, quantity, unit) | Returns the date resulting from the sum of the date plus the quantity in units. | • date (required): The starting date. • quantity (required): The amount to add. • unit (required): The unit of time. Valid units are ‘d’, ‘w’, ‘m’, ‘q’, and ‘y’. | |
| dateSub(date, quantity, unit) | Returns the date resulting from the difference of the date plus the quantity in units. | • date (required): The starting date. • quantity (required): The amount to subtract. • unit (required): The unit of time. Valid units are ‘d’, ‘w’, ‘m’, ‘q’, and ‘y’. | |
| 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. | • start_date (required): The start date (inclusive). • end_date (required): The end date (exclusive). • weekends (optional): 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’. • holidays (optional): A list of holidays as a string of comma separated dates in the format YYYY-MM-DD. Default is empty. |