Functions reference

Below is a list of functions available to use withing an Expression :

Arithmetic

Those are simple mathematical functions used to make calculations.

Add `+`

(Number1 + Number2)

Sum of Number1 and Number2

Substract `-`

(Number1 - Number2)

Substract Number1 and Number2

Multiply `*`

(Number1 * Number2)

Multiply Number1 and Number2

Divide `/`

(Number1 / Number2)

Divide Number1 by Number2

Modulo `%`

(Number1 % Number2)

Modulo of Number1 and Number2

Absolute value `Abs`

Abs(Number1)

Absolute value of Number1

Square Root `Square Root`

Square Root(Number1)

Square Root of Number1

Exponential `Exp`

Exp(Number1)

Exponential of Number1

Logarithm `Log`

Log(Number1)

Natural logarithm of Number1

Power `Power`

(Number1 Power Number2)

Number1 raised to power Number2

Rounding `Round`

(Number1 Round Decimals)

Number1 rounded to the nearest Decimals

Sign `Sign`

Sign(Number1)

Number indicating the sign of Number1 :

-1 if Number1 < 0
0 if Number1 = 0
+1 if Number1 > 0

Boolean

Those functions are boolean operators used in logical expressions (with the If function for example).

Conjunction `And`

(Expression1 And Expression2)

Logical conjunction of boolean Expression1 and Expression2

Disjunction `Or`

(Expression1 Or Expression2)

Logical disjunction of boolean Expression1 or Expression2

Negation `Not`

Not(Expression1)

Logical negation of boolean Expression1

Aggregation

These Aggregation functions play a special role in Expressions, please see chapters Aggregated Expressions and Expressions Troubleshooting
In particular, they apply differently on each Widget’s result set differently, depending on the grouping configured in the Widget.

Average `Avg`

Avg(Field1)

Average of all values in Field1

Count `Count`

Count(Field1)

Count all non- NULL values in Field1

Distinct Count `Distinct Count`

Distinct Count(Field1)

Count all distinct and non- NULL values in Field1

Maximum `Max`

Max(Field1)

Maximum of all values in Field1

Minimum `Min`

Min(Field1)

Minimum of all values in Field1

Summation `Sum`

Sum(Field1)

Sum total of all values in Field1

Standard Deviation `STDev`

STDev(Field1)

Statistical standard deviation of all values in Field1

Standard Deviation for population `STDevP`

STDevP(Field1)

Statistical standard deviation for the population of all values in Field1

Variance `Var`

Var(Field1)

Statistical variance of all values in Field1

Variance for population `VarP`

VarP(Field1)

Statistical variance for the population of all values in Field1

Geometry

Those are simple mathematical functions used to make calculations.

π `Pi`

Pi

Mathematical constant Pi

Sine `Sin`

Sin(Number1)

Sine of Number1

Cosine `Cos`

Cos(Number1)

Cosine of Number1

Tangent `Tan`

Tan(Number1)

Tangent of Number1

Arc Cosine `ACos`

ACos(Number1)

Inverse of Cosine of Number1

Arc Sine `Asin`

Asin(Number1)

Inverse of Sine of Number1

Arc Tangent `ATan`

ATan(Number1)

Inverse of Tangent of Number1

Degrees `Degrees`

Degrees(Number1)

Convert Number1 radians to degrees

Radians `Radians`

Radians(Number1)

Convert Number1 degrees to radians

String

Those are functions used to manipulate strings and characters.

Character `Char`

Char(Number1)

Convert the Number1 integer ASCII code to a character value

Concatenate `Concat`

(String1 Concat String2)

Concatenate String1 and String2 to form a single string

Contains `Contains`

(String1 Contains String2)

Search String1 for any occurrence of String2 and return :

true or 1 if String1 contains occurrences of String2
false or 0 otherwise

Starts with `Starts with`

(String1 Starts with String2)

Check if String1 starts with String2 and return :

true or 1 if String1 starts with String2
false or 0 otherwise

Ends with `Ends with`

(String1 Ends with String2)

Check if String1 ends with String2 and return :

true or 1 if String1 ends with String2
false or 0 otherwise

Left `Left`

(String1 Left Number1)

Return the Number1 left-most characters of String1

Right `Right`

(String1 Right Number1)

Return the Number1 right-most characters of String1

Length `Length`

Length (String1)

Return the number of characters for String1

Lower `Lower`

Lower (String1)

Return String1 as lower-case

Upper `Upper`

Upper (String1)

Return String1 as upper-case

RTrim `RTrim`

RTrim (String1)

removes right trailing spaces from String1

LTrim `LTrim`

LTrim (String1)

removes left trailing spaces from String1

Substring `Substring`

Substring (String1 , Number1 , Number2)

Return part of String1 starting from Number1 with Number2 number of character

Date

Now `Now`

Now

Return current date and time

Today `Today`

Today

Return current date

Day `Date1`

Day(Date1)

Returns the day of the month ( from 0 to 31 ) from Date1

Hour `Hour`

Hour (Date1)

Returns the Hour of the time (from 0 to 23) from Date1.

Minute `Minute`

Minute (Date1)

Returns the minute of the time (from 0 to 59) from Date1.

Second `Second`

Second (Date1)

Returns the second of the time (from 0 to 59) from Date1.

Month `Month`

Month (Date1)

Returns the month of the year (from 1 to 12) from Date1.

Month Year `MonthYear`

Month Year (Date1)

Returns the month and the year of the date from Date1.

Year `Year`

Year (Date1)

Returns the year of the date from Date1.

Week `Week`

Week (Date1)

Return the week number (from 1 to 52) from Date1.

Quarter `Quarter`

Quarter (Date1)

Return the Quarter of the year (from 1 to 4) from Date1.

Quarter Year `QuarterYear`

Quarter Year (Date1)

Return Year and quarter of the date from Date1.

Day of week `Dayofweek`

Day of week (Date1)

Return the number of the day during the week (from 1 to 7) from Date1.

Day of year `Dayofyear`

Day of year (Date1)

Return the number of the day during the year (from 1 to 365) from Date1.

Add Day `AddDay`

(Number1 Add day Date1)

Add Number1 Days to the Date1

Date difference `Datedifference`

(Date1 Date difference Date2)

Return Number of day between Date1 and Date2

Date `Date`

Date (String1)

Return Date from String1

Datetime `Datetime`

Datetime (String1)

Return Date and time from String1

Logical

= `equal`

(Number1 = Number2)

Logical check between Number1 and Number2 :

0 if numbers are different
1 if numbers are equal

!= `notequal`

(Number1 != Number2)

Logical check between Number1 and Number2 :

0 if numbers are equal
1 if numbers are different

if `if`

if(Field1, String1, String2)

Logical check if Field1 is true (=1) :

0 Return ***String1***
1 Return ***String2***

if null `ifnull`

(Field1 if null Field2)

Return ***Field2*** if ***Field1*** is null.
It can be used as fallback value

is null `isnull`

is null(Field1)

Logical check if Field1 is null

Null Return 0
Not null Return 1

is not null `isnull`

is not null(Field1)

Logical check if Field1 is not null

Null Return 1
Not null Return 0

> `Upper`

(Number1 > Number2)

Logical check if Number1 and Number2 :

0 if Number1 is not upper than Number2
1 if Number1 is upper than Number2

>= `UpperEqual`

(Number1 >= Number2)

Logical check if Number1 and Number2 :

0 if Number1 is not upper than Number2
1 if Number1 is equal or upper than Number2

< `Lower`

(Number1 < Number2)

Logical check if Number1 and Number2 :

0 if Number1 is not lower than Number2
1 if Number1 is lower than Number2

<= `LowerEqual`

(Number1 <= Number2)

Logical check if Number1 and Number2 :

0 if Number1 is not lower than Number2
1 if Number1 is equal or lower than Number2

Like `Like`

(Field1 Like Pattern)

Pattern maching.

1 if Field1 is equal to Pattern
0 if Field1 is not equal to Pattern
Please refer to https://www.w3schools.com/sql/sql_like.asp for full detail on usage.

In `In`

(Field1 In Field2)
Value comparator over a list of values. 
* @1@ if @Field1@ is contained in @Field2@
* @0@ if @Field1@ is not contained in @Field2@
https://www.w3schools.com/sql/sql_in.asp for full detail on usage. 
.

Not In `NotIn`

(Field1 Not InField2)

Value comparator over a list of values.

0 if Field1 is contained in Field2
1 if Field1 is not contained in Field2

Between `Between`

(Number1 Between  Number2 , Number2)

Return 1 (true) if Number1 is between Number2 and Number3 else return 0 (false)

Last modified: Jul 28, 2023

Aggregated Expressions

Expressions Troubleshooting

Feedback

Post your comment on this topic.

Arithmetic

Add +

Substract -

Multiply *

Divide /

Modulo %

Absolute value Abs

Square Root Square Root

Exponential Exp

Logarithm Log

Power Power

Rounding Round

Sign Sign

Boolean

Conjunction And

Disjunction Or

Negation Not

Aggregation

Average Avg

Count Count

Distinct Count Distinct Count

Maximum Max

Minimum Min

Summation Sum

Standard Deviation STDev

Standard Deviation for population STDevP

Variance Var

Variance for population VarP

Geometry

π Pi

Sine Sin

Cosine Cos

Tangent Tan

Arc Cosine ACos

Arc Sine Asin

Arc Tangent ATan

Degrees Degrees

Radians Radians

String

Character Char

Concatenate Concat

Contains Contains

Starts with Starts with

Ends with Ends with

Left Left

Right Right

Length Length

Lower Lower

Upper Upper

RTrim RTrim

LTrim LTrim

Substring Substring

Date

Now Now

Today Today

Day Date1

Hour Hour

Minute Minute

Second Second

Month Month

Month Year MonthYear

Year Year

Week Week

Quarter Quarter

Quarter Year QuarterYear

Day of week Dayofweek

Day of year Dayofyear

Add Day AddDay

Date difference Datedifference

Date Date

Datetime Datetime

Logical

= equal

!= notequal

if if

if null ifnull

is null isnull

is not null isnull

> Upper

Add `+`

Substract `-`

Multiply `*`

Divide `/`

Modulo `%`

Absolute value `Abs`

Square Root `Square Root`

Exponential `Exp`

Logarithm `Log`

Power `Power`

Rounding `Round`

Sign `Sign`

Conjunction `And`

Disjunction `Or`

Negation `Not`

Average `Avg`

Count `Count`

Distinct Count `Distinct Count`

Maximum `Max`

Minimum `Min`

Summation `Sum`

Standard Deviation `STDev`

Standard Deviation for population `STDevP`

Variance `Var`

Variance for population `VarP`

π `Pi`

Sine `Sin`

Cosine `Cos`

Tangent `Tan`

Arc Cosine `ACos`

Arc Sine `Asin`

Arc Tangent `ATan`

Degrees `Degrees`

Radians `Radians`

Character `Char`

Concatenate `Concat`

Contains `Contains`

Starts with `Starts with`

Ends with `Ends with`

Left `Left`

Right `Right`

Length `Length`

Lower `Lower`

Upper `Upper`

RTrim `RTrim`

LTrim `LTrim`

Substring `Substring`

Now `Now`

Today `Today`

Day `Date1`

Hour `Hour`

Minute `Minute`

Second `Second`

Month `Month`

Month Year `MonthYear`

Year `Year`

Week `Week`

Quarter `Quarter`

Quarter Year `QuarterYear`

Day of week `Dayofweek`

Day of year `Dayofyear`

Add Day `AddDay`

Date difference `Datedifference`

Date `Date`

Datetime `Datetime`

= `equal`

!= `notequal`

if `if`

if null `ifnull`

is null `isnull`

is not null `isnull`

> `Upper`

>= `UpperEqual`

< `Lower`

<= `LowerEqual`

Like `Like`

In `In`

Not In `NotIn`

Between `Between`