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 #
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 Round (value, n) Expression
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 Related Functions #
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