Calculations
Eaze formulas are used for executing cell calculations in the visual. This section describes how to use the function library to create formulas that perform custom row and column calculations.
Using functions in column calculations
All column calculations support using functions to perform it. A function can take one or more inputs (arguments). An argument can be a hardcoded value, for example, a number, a string (text), or an argument can be a cell value.
Function arguments are separated by commas. This differs from Excel, where semicolons are argument separators.
As an example, we'll use the CAGR function to compute growth. The CAGR function takes 3 arguments. The beginning and ending value + the number of years, so it will look like this:
CAGR(beginningValue, endingValue, numOfYears)
- In edit mode, create a custom column or override a column calculation.
- In the formula editor, type CAGR(.
- Click a cell to specify the beginning value column. The column name will be automatically added to the formula in the editor.
- Type a comma (,) in the editor after the beginning value column, to spit the first and second argument.
- Click a cell to specify the ending value column.
- Type a comma in the editor after the ending value column to split the second and third arguments.
- Enter the number of years as the third argument, for example, 2.
- Type ")" to close the function. It should look something like this CAGR([Actual_2018], [Actual_2020], 2)
Operators
Comparison operators
| Operator | Description |
|---|---|
| = | Assignment operator. Assigns the value on the right hand side to the variable or cell on the left hand side. Example X = 10; @Amount[ItemID == "A"] = 25.6; |
| > | Greater than operator. Checks if the value on the left side is greater than the value on the right side. Example 20 > 10 returns true. 10 > 20 returns false. |
| >= | Greater than or equal. Checks if the value on the left side is greater than, or equal to the value on the right side. |
| < | Less than operator. Checks if the value on the left side is less than the value on the right side. Example 20 < 10 returns false. 10 < 20 returns true. |
| <= | Less than or equal operator. Checks if the value on the left side is less than, or equal to the value on the right side. |
| == | Equals operator. Checks if the value on the left side is equal to the value on the right side. Example 1 == 1 returns true. 1 == 2 returns false. |
| != | Not equals operator. Checks if the value on the left side does not equal the value on the right side. Example 1 != 1 returns false. 1 != 2 returns true. |
Logical operators
| Operator | Description |
|---|---|
| && | Conditional AND operator. Performs a logical AND of its left and right operands. The right operand is only evaluated if the left operand is true. |
| || | Conditional OR operator. Performs a logical OR of its left and right operands. The right operand is only evaluated if the left operand is false. |
Arithmetic operators
| Operator | Description |
|---|---|
| + | Binary operator + for numeric types sums the operands. Binary operator + also concatenates strings if one of the operands is a string. You can also use the CONCAT function to concatenate strings. Example 1 + 2 returns 3. |
| - | Binary operator – subtract the second operand from the first. Example 2 – 1 returns 1 |
| * | The multiplication operator computes the product of its operands. Example 2 * 2 returns 4 |
| / | The division operator divides its first operand by its second operand Example 2 / 2 returns 1. |
| % | The modulus operator computes the remainder after dividing its first operand by its second operand. Example 10 % 2 returns 0 |
Primary operators
| Operator | Description |
|---|---|
| . (x.y) | Member access. Example "xyx".substring(…) |
| X?.y | Null conditional member access. Returns null if the left hand operand is null. Example X = null; Y = X?.substring(…) |
Unary operators
| Operator | Description |
|---|---|
| + (+x) | Returns the value of the operand |
| - (-x) | Numeric negation Example Y = -X; |
| ! (!x) | Logical negation Example X = !true; (X becomes false) |
Other binary operators
| Operator | Description |
|---|---|
| ?? | Null coalescing operator. The ?? operator checks whether the value provided on the left side of the expression is null, and if so it returns an alternate value indicated by the right side of the expression. If the value provided on the left side of the expression isn't null, then it returns the original value Example null ?? "Hello" returns the string "Hello". 0 ?? "Hello" returns 0 |
Keywords
| Keyword | Description |
|---|---|
| false | Boolean false value |
| true | Boolean true value |
| null | Represents a null reference |
| this | Returns a reference to the current execution context with the following properties: Rows: any[] (JavaScript array) |
Logical functions
| Function | Description |
|---|---|
| NOT(<expression>) | |
| IF(<condition>,<true-expression>,<false-expression>) | |
| TRUE() | Returns true |
| FALSE() | Returns false |
| COALESCE(…args) | Returns the first argument that is not null. Example COALESCE(null,"a",2) returns "a" |
| FIRSTNOTNULL(…args) | Same as COALESCE |
| ISNULL(<check-expression>,<replacement-expression>) | If check-expression is null, returns replacement-expression, otherwise check-expression is returned. Example ISNULL(null,1) returns 1 ISNULL(10 * 1, 100) returns 10 |
| ISNULL(<check-expression>) | If check-expression is null, returns true, otherwise false. Example ISNULL(null) returns true |
| ISNULLORZERO(<check-expression>,<replacement-expression>) | If check-expression is null or 0, returns replacement-expression, otherwise check-expression is returned. |
| ISNULLORZERO(<check_expression>) | If check-expression is NULL or 0, returns true, otherwise false. Example ISNULLORZERO(null) returns true |
| ISNUMBER(value) | Checks if the data type of value is a number data type. Example ISNUMBER(1) returns true ISNUMBER("2") returns false |
| ISNUMERIC(value) | Checks whether value is a number or can be converted to a number. Example ISNUMERIC(1) returns true ISNUMERIC("2") returns true ISNUMERIC("a") returns false |
| ISERROR(<expression>) | Returns true if evaluation of expression results in an error. |
| IFERROR(<check-expression>,<replacement-expression>) | If check-expression results in an error, returns replacement-expression, otherwise check-expression is returned. |
| ISNULLOREMPTYSTR(<expression>) | Returns true if expression is null or an empty string. Example ISNULLOREMPTYSTR(null) returns true ISNULLOREMPTYSTR(@ProductID[AccountID == "A100" && MarketID == "NO-V"]) |
| NZ(<check-expression>) | If the check-expression is null or an empty string, returns 0, otherwise the check-expression is returned. Example NZ(null) returns 0 NZ(1) returns 1 NZ(" ") returns 0 |
Financial functions
| Function | Description |
|---|---|
| CAGR(beginningValue, endingValue, years) | Calculates compound annual growth rate. |
| YoY(pastValue, presentValue) | Calculates year-over-year growth. |
| AMORLINC(cost : number, date_purchased : Date, first_period : Date, salvage : number, period : number, rate : number [,basis : number]) | |
| AMORLINCMTH(cost : number, date_purchased : Date, first_period : Date, salvage : number, period : Date, rate : number [,basis : number]) |
Math / Trig functions
| Function | Description |
|---|---|
| ABS(x : number | <expression>) | Returns the absolute value of the argument. Example ABS(-1) returns 1 |
| ACOS(x : number | <expression>) | Returns the inverse cosine of x. Example ACOS(0.65) returns 0.863211… |
| ASIN(x : number | <expression>) | Returns the inverse sine of x. Example ASIN(0.65) returns 0.70758… |
| ATAN(x : number | <expression>) | |
| ATAN2(x : number | <expression>, y : number | <expression>) | Returns the angle (in radians) from the x-axis to a point. |
| CEILING(x : number | <expression>) | Returns x, rounded upwards to the nearest integer. |
| COS(x : number | <expression>) | |
| EXP(x : number | <expression>) | Returns E (the base of natural logarithms) to the power of x. |
| FLOOR(x : number | <expression>) | Returns x, rounded downward to the nearest integer. |
| LN(x : number | <expression>) | Returns the natural logarithm (base e) of x. |
| LOG(x : number | <expression>) | |
| LOG10(x : number | <expression>) | |
| MOD(x : number | <expression>, y : number | <expression>) | Returns x modulus y. |
| PI() | Returns PI. |
| POW(x : number | <expression>, y : number | <expression>) | Returns x to the power of y. |
| RAND() | Returns a pseudorandom number between 0 and 1. |
| ROUND(x : number | <expression>) | Returns x, rounded to the nearest integer. |
| SIGN(x : number) | Returns the sign for the number x. -1 for negative values, 1 for positive values. |
| SIN(x : number | <expression>) | Returns sine of x. |
| SQRT(x : number | <expression>) | Returns the square root of x. |
| SUM(…x : number | <expression>) | Returns the sum of the arguments. Example SUM(1,2,3) returns 6 |
| TAN(x : number | <expression>) | Returns the tangent of an angle. |
| NUM_MIN() | Returns the minimum value of the Number type. |
Statistical functions
| Function | Description |
|---|---|
| AVERAGE(…x : number | <expression>) | Returns the average of the numbers passed to the function. Only numbers and arrays of numbers are processed. |
| AVERAGEA(…x : number | <expression>) | Returns the average of the numbers passed to the function. Numbers, arrays of numbers and values representing numbers (such as true, false and string representation of numbers) are processed. |
| COUNT(…x : number | <expression>) | Counts the number of numeric values passed to the function. Only numbers and arrays of numbers are processed. Example COUNT(1,2,"test") returns 2 COUNT(ARRAY(1,2,3)) returns 3 |
| COUNTA(…x : number | <expression>) | Counts the number of logical values passed to the function. Numbers, arrays of numbers and values representing numbers (such as true, false and string representation of numbers) are processed. Example COUNTA(1,2,"3") returns 3 COUNTA(1,2,"x") returns 3 COUNTA(1,2,null) returns 2 COUNTA(ARRAY(1,2,3,4,true,"")) returns 6 |
| COUNTBLANK(…x : number | <expression>) | Counts the number of null values passed to the function. Example COUNTBLANK(null) returns 1 COUNTBLANK(ARRAY(1,null,1,null)) returns 2 |
| MAX(…x : number | <expression>) | Returns the max value of the numeric values passed to the function. Only numbers and arrays of numbers are processed. Example MAX(1,4,3,true,null) returns 4 |
| MAXA(…x : number | <expression> | boolean | string) | Returns the max value of the numbers or numeric representations of the values passed to the function. Example MAXA(false,null) returns 0 MAXA(0,true) returns 1 |
| MIN(…x : number | <expression>) | |
| MINA(…x : number | <expression> | boolean | string) | |
| STDEV(…x : number | <expression>) | |
| STDEVA(…x : number | <expression>) | |
| STDEVP(…x : number | <expression>) | |
| STDEVPA(…x : number | <expression>) | |
| VAR(…x : number | <expression>) | |
| VARA(…x : number | <expression>) | |
| VARP(…x : number | <expression>) | |
| VARPA(…x : number | <expression>) |
Text functions
| Function | Description |
|---|---|
| CONCAT(…t : string) | Concatenates a comma separated list of strings. CONCAT("a","b","c") returns the string "abc". |
| SUBSTRING(input : string, start : number, length? : number) | Returns a substring of the input string. SUBSTRING("Hello", 1) returns the string "ello". SUBSTRING("Hello", 1, 2) returns the string "el". |
| SPLIT(input : string, delimiter : string) | Returns an array of strings that contains the substrings of the input string that are delimited by the delimiter argument. SPLIT("Hi, everyone", ",") returns ["Hi", "everyone"]. |
| LEFT | |
| LEN | |
| LOWER | Converts all characters in a string to lower case. |
| REPLACE | |
| RIGHT | |
| TOSTRING(value) | Converts a value to a string. For example, the number 100.123 is converted to the string "100.123". |
| TOSTRING(value, formatString) | Similar to the Excel TEXT-function, converts a value to a string using the specified format. When the value is a number, valid format strings are those supported by the numeraljs formatting library – http://numeraljs.com. When the value is a Date, valid format strings are those supported by the momentjs formatting library – http://momentjs.com. Example TOSTRING(DATE(2016,1,1), "YYYYMMDD") returns "20160101". |
| TRIM(input) | Removes leading and trailing whitespace characters from a string. |
| UPPER(input) | Converts all characters in a string to upper case. |
| TONUMBER(value) | Converts a string to a number. If the string cannot be converted to a number, null is returned. |
| NEWLINE() | Returns the newline character. |
Date functions
| Function | Description |
|---|---|
| DATE(year [,month,day,hours,min,sec, ms]) | Creates a Date object from the specified arguments. |
| DATEVALUE(year [,month,day,hours,min,sec, ms]) | Creates a Date object from the specified arguments. |
| DATE(expression) | Creates a Date object by evaluating expression. |
| TODATE(expression[,input format]) | Converts a date string or number to a Date object. If you do not specify the input format, the date string must be an ISO 8601 date string. Example TODATE("2001-01-01") returns a Date object representing January 1st, 2001. Example TODATE("20.12.2006", "DD.MM.YYYY") returns a Date object representing Dec 20th 2006. |
| NOW() | Returns the current Date. |
| FORMATDATE(date : Date | string, format : string) | Returns a string representation of a date using the specified format and the current locale. The formatting function supports momentjs formats. If the date passed to the function is a string and not a date object, the format of the string is expected to be ISO 8601. |
| TZUTC(date) | |
| TZLocal(date) |
System functions
| Function | Description |
|---|---|
| ARRAY(…args) | Creates an array of values. Example ARRAY(1,4,"test") |
| NEWID() | Creates a unique id with the length of 36 characters, on the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx. |
| ENVIRONMENT() | Returns the browser window object. |
| EVAL(expression : string) | Dynamically evaluates an expression and returns the value. The argument passed to EVAL can be a string or an expression returning a string. See the EVAL examples below. |
| JsonParse(value) | Parses a JSON string, constructing the JavaScript value or object described by the string. |
| JsonStringify(text) | Converts a JavaScript value to a JSON string. |
EVAL examples
EVAL("1 + 2") returns 3
EVAL(CONCAT("1+", "2")) returns 3
You can also use the EVAL function to execute formulas embedded in a data set. For example, if the data set contains a Formula column with valid Eaze expressions, you can create dynamically executed expressions like this:
@Total[] = EVAL(@Formula[]);
So if the Formula column contains the expression @P01[] + @P02[], the expression will be equivalent to @Total[] = @P01[] + @P02[];.
Row collection functions
| Function | Description |
|---|---|
| ADDROWFIRST(JSON object) | Adds a row at the beginning of the data set bound to the grid. Example ADDROWFIRST({"ObjectId":-1, "AccountID":"F100", "ProductID":"P-001","Total":0,"P01":-3000}) |
| ADDROWLAST(JSON object) | Adds a row at the end of the data set bound to the grid. Example ADDROWLAST({"ObjectId":-1, "AccountID":"F100", "ProductID":"P-001", "Total":0, "P01":-3000}) |
| ADDROWAFTER(address expression, JSON object) | Adds a row after the first row matching the address expression. Example Add a row after the first row where AccountID equals A3000: ADDROWAFTER(AccountID == "A3000", {"ObjectId":-1,"AccountID":"F100", "ProductID":"P-001", "Total":0, "P01":-3000}) |
| ADDROWBEFORE(address expression, JSON object) | Adds a row before the first row matching the address expression. Example Add a row before the first row where AccountID equals A3000 and Amount > 5000: ADDROWBEFORE(AccountID == "A3000" && Amount > 5000, {"ObjectId":-1,"AccountID":"F100","ProductID":"P-001","Total":0,"P01":-3000}) |
| IsSummaryRow(…) | Returns true if the row in the current call context is a summary row. Example IsSummaryRow(GetCallContextRow()) returns true if the row currently being evaluated by the Eaze runtime is a summary row. |
| IsRowLocked(…) | Returns true if a row is locked. Example IsRowLocked(GetCallContextRow()) |
Cell and row styling functions
| Function | Description |
|---|---|
| SETROWSTYLE(row address expression, style name : string) | Sets the style of rows matching the row address expression. Example 1 Apply a specific css class ("ProductGroup1") to all rows where ProductID equals XYZ: SETROWSTYLE(ProductID == "XYZ", "ProductGroup1"); Example 2 Apply a specific css class ("StyleA") to all rows where ProductID starts with the letter 3: SETROWSTYLE(STARTSWITH(ProductID, "3"), "StyleA"); |
| SETCELLSTYLE(row address expression, column name : string, css class name : string) | Sets the style of cells in a specific column matching the row address expression. Example Apply a specific css class ("RedCell") to cells in the "Total" column if Total is less than 1000: SETCELLSTYLE(Total < 1000, "Total", "RedCell") |
Offset
With Add Column Formula or Add Measure, you get a formula field. In this, you can now use the Offset function.
Goal: add a measure that compares current month against last month.
Steps:
Right-click a column in February (we need a value for the period before, which January does not have in this view).
Click on a value in the Actuals column under February to get the current value.
Hold the Shift-key while clicking a value in the Actuals column under January. The orange arrow in February indicates this is the starting point for the formula. Any offsets will be decided based on its position relative to the orange arrow: clicking January while holding Shift will be (offset-1), while clicking March will be (offset+1).
The offset function works with any field you add to the Column header, not only Months or years.
Table functions
ColumnHeader()
Returns the caption of the column.
ColumnHeaderParent()
Returns the parent caption of the column.
RowHeader()
Returns the caption / text of the row. Can be used in an IF statement, or a Conditional field.
RowLevel()
Returns the level of the row. Can be used in an IF statement, or a Conditional field. For example, apply a style to specific levels only.
Row().formatString
Returns the format string applied to the row.
Row().style
Returns the styles applied to each row. Can be used in formulas or conditional formatting.
RowHeaderParent()
Returns the name of the parent of each row. Can be used in formulas or conditional formatting conditions.
Available styles to reference
- stylesTotal
- stylesSubtotal
- stylesKPI
- overline
- underline
- custom1
- custom2
- custom3
- custom4
- custom5
- custom6
- bold
- hidden
Extra flexibility by using JSON-strings
By using a JSON-string, you can call all parts of the string by using Row()."columnName".
The default JSON-string consists of:
- id
- displayName
- formula
- style
- formatString
- signFactor
{
"id": "L1Sum",
"displayName": "1Sum - Reportline",
"formula": "L10+L12",
"style": "bold overline",
"formatString": "#,0",
"signFactor": 1
}
This can be extended with any columns you want, by adding a new column name and a value to the JSON-string:
{
"id": "L1Sum",
"displayName": "1Sum - Reportline",
"formula": "L10+L12",
"style": "bold overline",
"formatString": "#,0",
"signFactor": 1,
"IsIncome": 1
}
That extra column can then be referenced using the formula Row().IsIncome, and would return the value 1 — which can be used in conditional formatting.
For example: if value is greater than 0 and IsIncome == 1, apply a style with green font color.