Expressions, Operators, & Functions

The Assette Data Object Editor provides a range of expressions, operators, and functions that enable you to manipulate and calculate data effectively similar to Microsoft Excel. Below is a list of available Expressions within the Data Object Editor.

Mathematical Operators #

ADD Operator #

In the Assette Data Object Editor, addition is performed using the + operator. This operator allows you to add numerical values together to calculate sums. Please see the “ADD” Expression a more in-depth review of the “ADD” function.

value1 + value2

MINUS Operator #

In the Assette Data Object Editor, addition is performed using the “-” operator. This operator allows you to subtract numerical values from one another. Please see the “Minus” Expression a more in-depth review of the “Minus” function.

10 - 9

MULTIPLY Operator #

In the Assette Data Object Editor, multiplication is performed using the “*” operator. This operator allows you to multiply numerical values to one another. Please see the “Multiply” Expression a more in-depth review of the “Multiply” function.

1 * 2

DIVIDE Operator #

In the Assette Data Object Editor, division is performed using the “/” operator. This operator allows you to divide numerical values by one another. Please see the “Minus” Expression a more in-depth review of the “Minus” function.

1000 / 10

Grouping Operators #

GROUPING Operator #

In the Assette Data Object Editor, grouping is performed using the “( )” operators. This operator allows you to group numerical values. Please see The Grouping Operator “()” a more in-depth review of the “Grouping” operator. The open and close parenthesis “( )” can be used as a grouping to group and/or nest and must always be used as a set.

 ( ( 3 + 10 ) * 2) / 3

Logical Operators #

The “Equals” Operator #

== Equals

"String" == "String"

The “Less Than” Operator #

< Less than

 9 < 8

The “Less Than or Equal to” Operator #

<= Less than or equal to

8 < 9

The “Greater Than” Operator #

> Greater than

9 > 8

The “Greater Than or Equal to” Operator #

>= Greater than or equal to

8.5 >= 8

The “Not Equal to” Operator #

In the Assette Data Object Editor, addition is performed using the keyword “<>”. This keyword allows users to check if two values are not equal. Please see The “<> Not Equal To” Operator more in-depth review of the “<>” function.

NOT EQUAL TO “<>”

100 <> 12

The “AND” Operator #

In the Assette Data Object Editor, addition is performed using the keyword “and”. This keyword allows users to combine multiple logical conditions in a single logical statement. Please see The “AND” Expression a more in-depth review of the “and” function.

(price > 100) and (price <= 1000)

The “OR” Operator #

OR Function

(price > 100) or (price <= 1000)

The “NOT” Operator #

not Not or negate e.g.: not (price > 100)

Keywords #

The “NULL” Keyword #

null – null value (e.g. data point that is not available in the data source)

undefined – undefined (e.g. divide by zero) or unassigned value

true – Boolean true value

false – Boolean false value

Mathematical Functions #

MOD Function #

Mod(n1, n2) – Modulus of n1 / n2.

Mod(11,3)

Result: 2

ABS Function #

Abs(n) – Get the absolute value.

Abs(-10)

Result: 10

ROUND Function #

Round a value using standard bankers rounding e.g.

round(10.56, 1)

Result: 10.6

POWER Function #

Power(n1, n2) – Get n1 to the power n2. e.g. Power(10, 3) is 1000, Power(100,0.5) is 10

Conditional Functions #

HasValue Function #

HasValue(v1) – Check if the value of v1 is not null nor undefined

ValOrDef Function #

The ValOrDef Function return the value of v1, if v1 is not null or undefiend. Return defvaultValue otherwise.

ValOrDef(v1, defvaultValue)

Date #

Date(year, month, day) – Define a new date. e.g. Date(2023, 12, 31)

ToDate #

ToDate(dateAsString) – Convert a string to date, e.g. ToDate(“”)

Year #

Year(date) – Get the year of a date

Month #

Month(date) – Get the month (as a number between 1 to 12) of the date

Day #

Day(date) – Get the day of the month

DayOfWeek #

DayOfWeek(date) – Get the day of the week

Quarter #

Quarter(date) – Get the quarter of the year

AddToDate #

AddToDate(date, years: nyears, months: nmonths, days: ndays) – Add years, months and days to the date. e.g. AddToDate(date, days: -1) returns yesterday.

String Processing Functions #

StartsWith(mainString, subString) – Check if main string starts with the sub string, e.g. StartsWith( “ABCD”, “AB”) returns true.

StartsWith(mainString, subString, ignoreCase: true) – Check if main string starts with the sub string, ignoring case, e.g. StartsWith( “ABCD”, “ab”, ignoreCase: true) returns true.

EndsWith(mainString, subString) – Check if main string ends with the sub string, e.g. EndsWith( “ABCD”, “CD”) returns true.

EndsWith(mainString, subString, ignoreCase: true) – Check if main string ends with the sub string, ignoring case, e.g. StartsWith( “ABCD”, “cd”, ignoreCase: true) returns true.

Contains(mainString, subString) – Check if main string contains the sub string, e.g. Contains( “ABCD”, “BC”) returns true.

CONTAINS Function #

Contains(mainString, subString, ignoreCase: true) – Check if main string contains the sub string, ignoring case, e.g. Contains( “ABCD”, “bc”, ignoreCase: true) returns true.

ISEMPTY Function #

IsEmpty(st) – Check if the string is empty

NUMBER Function #

Number(st) – Convert a string into a number

TRIM Function #

Trim(st) – Trim white spaces of a string

UPPER Function #

Upper(st) – Convert a string to upper case

LOWER Function #

Lower(st) – Convert a string to lower case

SUBSTRING Function #

SubString(st, start: nchars, end: nchars) – Extract a sub string from a string. e.g. SubString(“ABCDEF”, start: 2) is “CDEF”, SubString(“ABCDEF”, start: 2, end: 2) is CD, SubString(“ABCDEF”, start: -2) is EF.

LEN Function #

Len(st) – Get the length of a string