🧮Expression Builder

Auditors are experts at writing formulas, crafting them in spreadsheet tools, most days of the week. AuditBoard Analytics Expression Builder works on the exact same principles.

Remember that while there are common principles across different data tools, AuditBoard Analytics may differ in terms of the syntax and functions available.

Formulas

In AuditBoard Analytics, a formula is a statement that's created to calculate new values or modify existing values in your data. It's commonly used in the Add Columns tool, which allows you to create new columns by defining an expression or series of expressions.

For instance, a formula in AuditBoard Analytics could be IF [Sales] > 1000 THEN 'High' ELSE 'Low' ENDIF, which would return "High" for sales over 1000 and "Low" for all other sales.

Expressions

An expression in AuditBoard Analytics is a combination of functions, operators, constants, fields (or columns), and the logical connections between them. These are used to perform calculations, manipulate data, or evaluate conditions.

Expressions are used inside formulas to perform specific operations. For instance, [Sales] > 1000 is an expression that evaluates whether the "Sales" field is greater than 1000.

Constants

A constant in AuditBoard Analytics is a fixed value that doesn't change. Constants can be numeric (2, 3.14), string ('Hello', 'West'), or boolean (True, False). For example, in the expression [Sales] * 1.05, 1.05 is a numeric constant.

Constants can be swapped out, when configured as a Parameter.

Operators

Operators in AuditBoard Analytics are symbols or keywords used within expressions to perform operations. See all operators with syntax.

They include mathematical operators (+, -, *, /), comparison operators (==, !=, >, <, >=, <=), and logical operators (AND, OR, NOT).

For example, in the expression [Sales] > 1000, the > is a comparison operator.

Functions

A function in AuditBoard Analytics is a predefined command that performs a specific operation or set of data tasks. Functions take certain inputs (known as arguments) and produce a result. See all functions available.

AuditBoard Analytics includes over 150 functions for different purposes, including aggregation, conversion, date/time calculations, logical arguments, math calculation, table manipulation, and text manipulation.

An example of a function is LEFT([Name], 3), which extracts the first three characters from the "Name" field.

Error Highlighting

When writing an expression, you may encounter errors. These errors are highlighted with a red underline—similar to how misspelled words are marked in a word processor. This visual cue helps you quickly identify and locate issues within your expression.