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 | This also works for date comparisons. For example: if(thisMonth() == ‘2025-01-01’,1,0) will return 1 if the current month is January 2025. |
| != | Not equal to | value1 != value2 | This also works for date comparisons. For example: if(thisMonth() != ‘2025-01-01’,1,0) will return 1 if the current month is not equal to January 2025. |
| > | Greater than | value1 > value2 | This also works for date comparisons. For example: if(thisMonth() > ‘2025-01-01’,1,0) will return 1 if the current month is after January 2025. |
| < | Less than | value1 < value2 | This also works for date comparisons. For example: if(thisMonth() < ‘2025-01-01’,1,0) will return 1 if the current month is before January 2025. |
| >= | Greater than or equal to | value1 >= value2 | This also works for date comparisons. For example: if(thisMonth() >= ‘2025-01-01’,1,0) will return 1 if the current month is on or after January 2025. |
| <= | Less than or equal to | value1 <= value2 | This also works for date comparisons. For example: if(thisMonth() <= ‘2025-01-01’,1,0) will return 1 if the current month is on or before January 2025. |
| AND | Logical AND | condition1 AND condition2 if(thisMonth()>=‘2021-01-01’ AND thismonth()<=‘2021-12-31’,1,0) | You can use parentheses to create nested or more complex logic. |
| OR | Logical OR | condition1 OR condition2 if(thisMonth()>=‘2021-01-01’ OR thismonth()<=‘2021-12-31’,1,0) | 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) | Always rounds a number down to the nearest whole number. | • value (required): The number to round. | roundUp is not natively supported, but you can implement it with -roundDown(-value) |
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 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). You can update type from the| Function | What it does | How to use it | Things to keep in mind |
|---|---|---|---|
| day(date_expression) | Returns a number corresponding to the day in the month of the date passed as an input (1-31) | date_expression (required): The date to extract the day of month for. day(‘2025-01-15’) day(dateDriver) | |
| weeknum(date_expression) | Returns a number corresponding to the week of the year for the date passed as an input (1-53) | date_expression (required): The date to extract the week of the year for. weeknum(‘2025-01-15’) weeknum(dateDriver) | |
| month(date_expression) | Returns a number corresponding to the month of the year for the date passed as an input (1-12) | date_expression (required): The date to extract the month of the year for. month(‘2025-01-15’) month(dateDriver) | |
| quarter(date_expression) | Returns a number corresponding to the quarter of the year for the date passed as an input (1-4) | date_expression (required): The date to extract the quarter of the year for. quarter(‘2025-01-15’) quarter(dateDriver) | |
| year(date_expression) | Returns a number corresponding to year of the date passed as an input | date_expression (required): The date to extract the year of. year(‘2025-01-15’) year(dateDriver) | |
| 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’ dateDiff(thisMonth(),‘2025-01-15’,‘m’) dateDiff(thisMonth(),dateDriver,‘m’) | 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’. dateAdd(thisMonth,1,‘m’) dateAdd(‘2025-01-15’,driver,‘m’) | |
| 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’. dateSub(thisMonth,1,‘m’) dateSub(‘2025-01-15’,driver,‘m’) | |
| 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. netWorkDays(thisMonth(), dateAdd(thisMonth(), 1, ‘m’), ‘0000011’, ‘2025-01-01,2025-01-02’) |
Date access 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, as a date. | date_expression (required): The date to find the start of month for. startOfMonth(‘2025-01-15’) startOfMonth(dateDriver) |
| endOfMonth(date_expression) | Returns the last day of the month for the date expression, as a date. | date_expression (required): The date to find the end of month for. endOfMonth(‘2025-01-15’) endOfMonth(dateDriver) |
| 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. |