Expressions/en

Expressions allows to manually create and edit Filters rules by using programming language.

Important: After manually editing the expression, the regular operators can not be used until you Clear All the filters;

Basic Expression Structures

Value Operands

The value operands are values from a data type that are used in expressions as variables.

Value type operand

Are numeric values of the float type (Real numbers domain). These values allow the use of an integer and a fraction part, separated by the dot (.) character (decimal separator).

 Examples: 1, 1.05, 2.55;

Text type operand

Are literal string data operands. These values allow the use of characters chain fields (strings), being delimited by the simple quote (') character.

 Examples: 'John Snow', 'California', 'Spaghetti';

Date type operand

Are values that express any date. It is possible to determine a date representing it as a string, being delimited by the simple quote (') character and separated by the bar (/) character. The default supported format is "dd/MM/yyyy".

 Examples: '01/01/2011', '29/02/2000';

Boolean type operand

Are values from the true or false kind. Usually used to validate or not a condition.

 Examples: true e false;

Identifier type operand

Are values that represents a field or variable in the expression. Used to make reference to data source columns, delimited by double quotes (").

 Examples: "City.City", "Product.Product";

Parenthesis

Used to group and separate expressions parts. Expressions inside parenthesis are processed first.

 Examples: ("Profit" / "Total Price" ) + 10

System variable type operand

Are system pre-defined variables. These values are replaced in run-time by pre-determined values, delimited by double quotes (") and preceded by star (#). By now only one variable is available: "#null" that indicates null values or fields.

Arithmetic Expressions

The arithmetic expressions relate two operands (value or another expression) to compose a sum, subtraction, multiplication or division.

Sum (+)

Sum two operands. Represented by the sum (+) character.

   Syntax: <operand/expression> + <operand/expression>. 
 Examples: "Taxes" + "Commissions";

Subtraction (-)

Subtract two operands. Represented by the subtract or hyphen (-) character.

   Syntax: <operand/expression> - <operand/expression>. 
 Examples: "Gross Sale" - "Taxes";

Multiplication (*)

Multiplicate two operands. Represented by the multiplication or star (*) character.

   Syntax: <operand/expression> * <operand/expression>. 
 Examples: "Amount" * "Unit Price";

Division(/)

Divides two operands. Represented by the divide or bar (/) character.

   Syntax: <operand/expression> / <operand/expression>. 
 Examples: "Total Value" / "Amount".

Relational Expressions

The relational expressions compare two operands (value or another expression) two evaluate if a condition is true or false. Usually used with decision functions, like the IF.

Equal (==)

Performs the equal comparison expression between two operands. Represented by the equal operator (==).

   Syntax: <expression> == <expression>. 
 Examples: "Unit Price" == "Total Price";

Different (!=)

Performs the different comparison expression between two operands. Represented by the different operator (!=).

   Syntax: <expression> != <expression>.
 Examples: "City.City" != 'New York';

Greater than (>)

Return true if the first expression is greater than the second expression and false otherwise. Represented by the greater operator (>).

   Syntax: <expression> > <expression>. 
 Examples: "Order Date" > '01/01/2011';

Greater or equal than (>=)

Return true if the first expression is greater than or equal to the second expression and false otherwise. Represented by the greater or equal operator (>=).

   Syntax: <expression> >= <expression>. 
 Examples: IF("Order Date" >= '01/01/2011';"Order Date";0);

Lesser than (<)

Return true if the the first expression is lesser than the second expression and false otherwise. Represented by the lesser operator (<).

   Syntax: <expression> < <expression>. 
 Examples: "Profit" < '0';

Lesser or equal than (<=)

Return true if the first expression is lesser than or equal to the second expression and false otherwise. Represented by the lesser or equal operator (<=).

   Syntax: <expression> <= <expression>. 
 Examples: IF("Order Date" <= '01/01/2011';"Order Date";0).

Logical Expressions

The logical expressions group two operands (boolean type or another expression) to compose a logic expression, with the objective to evaluate two boolean operands relation into a true or false result. Usually used with decision functions, like the IF, and with relational expressions.

And (&&)

Performs the logical operation AND between two boolean values. The two operands must be true for the result of the operation to be true. Represented by the AND operator (&&).

   Syntax: <expression> && <expression>. 
 Examples: IF((Gross Sales > '0') && (Seller == 'John Snow');'Sold';'Not Sold'), IF((City!="#null") && (Date<DAY_FROM_TODAY(0));1;0);

Or (||)

Performs the logical operation OR between two boolean values. One of the two operands must be true for the result of the operation to be true. Represented by the OR operator (||).

   Syntax: <expression> || <expression>. 
 Examples: IF((Gross Sales > '0') || (Sold == 'true');'Sold';'Not Sold'), IF((City!="#null") || (Date<DAY_FROM_TODAY(0));1;0);

Not (!)

Performs the denial logical operation for a boolean value. If the operand is true the NOT convert it to false (and the other way around). Represented by the NOT operator (!).

   Syntax: ! <expression>. 
 Examples: IF(!BETWEEN("Date", '01/01/2013', '31/12/2013');'Not 2013';'2013').

Auxiliary Functions

Basic Functions

The basic functions are system default functions and implements useful functionalities.

Abs

Returns the absolute value of a base value.

   Syntax: ABS(<expression>). Accept one parameter and process a numeric data type. 
 Examples: ABS(Gross Sale-Taxes);

Avg

Returns the average value of an array of values.

   Syntax: AVG(<expression1>;<expression2>[;<expression3>;...;<expressionN>]). Accept multiple parameter and process a numeric data type. 
           Can be used to return the average value of different columns from the same row of a table. 
 Examples: AVG(Taxes;Commissions;Costs);

Between

Check if a value is inside the boundaries of the parameters values.

   Syntax: BETWEEN(<expression_to_test>;<expression_bottom_limit>;<expression_top_limit>). Need three parameters (of any data type, but
           the three must be from the same type) and returns a boolean, true if value is inside the boundaries and false if not. 
 Examples: BETWEEN("Date";'01/01/2013';'31/12/2013'), BETWEEN("Gross Sales";'0';'1000.00');

If then else

Tests a boolean expression and returns a value in case it is true and another in case it is false.

   Syntax: IF(<expression_test>;<expression_for_true>;<expression_for_false>). Need three parameters, a boolean for test and returns any
           data type, provided that both are from the same type. Useful for logic tests where two different executions expressions are
           needed. 
 Examples: IF("Branch"=='SOUTH';'South';'Other Branch')
           IF("Profit"==0;0;Profit+Taxes);

Log

Returns the log value of a number on a set base.

   Syntax: LOG(<expression_number>[;<expression_base>]). Minimum one parameter and maximum two. If just one parameters is given, the 
           neperian log is returned. If the second parameter is also given, returns the log in the respective base. 
 Examples: LOG(Profit);

Max

Returns the greater value between two numbers.

   Syntax: MAX(<expression_value1>;<expression_value2>). Need two value parameters and returns a value data type. 
 Examples: "Total Value"/MAX("Quantity";"1");

Min

Returns the smaller value between two numbers.

   Syntax: MIN(<expression_value1>;<expression_value2>). Need two value parameters and returns a value data type. 
 Examples: "Total Value"/MIN("Quantity";"1");

Pow

Returns the power value of a number on a set exponent.

   Syntax: POW(<expression_number>[;<expression_exponent>]). Minimum one parameter and maximum two. If the exponent value is not given,
           use two (2) by default. 
 Examples: POW(Value1)+POW(Value1-Value2;3);

Random

Returns an aleatory number inside the boundaries of the limit values.

   Syntax: RANDOM(<expression_bottom_limit>;<expression_top_limit>). Need two parameters, the bottom and the upper limit of the random
           number to be generated. 
 Examples: RANDOM(0.0;1.0);

Sqrt

Returns the square root of a value.

   Syntax: SQRT(<expression>). Need one parameters, the value to be calculated. 
 Examples: SQRT('36').

Like

Returns true if the desired content is found in the list and false if the content is not found.

   Syntax: LIKE(<data_set>;<pattern>). Needs two parameters, the first is the set of data that will be queried. The second is the term one wishes to find, in the data set. Returns true or false.
 Examples: LIKE("Product";'%ghe%'). Returns true if in the Product dataset there is some containing gue. Otherwise, returns false.

Date Functions

The date functions are operations that use as parameters and return the result of the date data type.

Constants list

• • 0: DATE_FIELD_YEAR (Year part);
  • 1: DATE_FIELD_MONTH (Month part);
  • 2: DATE_FIELD_DAY (Day part);
  • 3: DATE_FIELD_FORTNIGHT (Fortnight part);
  • 5: DATE_FIELD_WEEK (Week part);

Date part

Return a part of a date.

   Syntax: DATE_PART(<expression_date>;<constant>). Need two parameters, the first is the reference date and the second the constant
           representing the desired part date: 0, 1, 2, 3 or 5 (see above constants list). 
 Examples: DATE_PART("Order Date";0) - returns the year part of the "Order Date";

Day from today

Return a backward (negative) or forward (positive) date counting from today.

   Syntax: DAY_FROM_TODAY(<expression_number_days>). Need one parameter, the number of days to add or subtract. 
 Examples: DAY_FROM_TODAY(-1) - returns the yesterday date;

First day of

Return the first day of a grouping. This function has two variations:

• First way:

    Syntax: FIRST_DAY_OF(<constant_grouping>;<expression_date>). Need two parameters, the first is the constant grouping in which you 
            want to obtain the first date: 0, 1, 2, 3 or 5 (see constants list) and the second is the anchor base date. 
  Examples: FIRST_DAY_OF(0;DAY_FROM_TODAY(0)) - first day of year (0) from today (DAY_FROM_TODAY(0)), FIRST_DAY_OF(1;"Order Date") -
            first day of month (1) based on the "Order Data";

• Second way:

    Syntax: FIRST_DAY_OF(<constant_grouping>;<constant_modify_date>;<modify_date_value>). Need three parameters, the first is the
            constant grouping in which you want to obtain the first date: 0, 1, 2, 3 or 5 (see constants list), the second is the modifier 
            of the base date from today: 0, 1, 2, 3 or 5 (see constants list) and the third is the amount to change the modifier. 
  Examples: FIRST_DAY_OF(1;0;-1) - first day of the current month (1) from last year (0;-1);

Last day of

Return the last day of a grouping. This function has two variations:

• First way:

    Syntax: LAST_DAY_OF(<constant_grouping>;<expression_date>). Need two parameters, the first is the constant grouping in which you 
            want to obtain the last date: 0, 1, 2, 3 or 5 (see constants list) and the second is the anchor base date. 
  Examples: LAST_DAY_OF(0;DAY_FROM_TODAY(0)) - last day of year (0) from today (DAY_FROM_TODAY(0)), LAST_DAY_OF(1;"Order Date") - 
            last day of month (1) based on the "Order Data";

• Second way:

    Syntax: LAST_DAY_OF(<constant_grouping>;<constant_modify_date>;<modify_date_value>). Need three parameters, the first is the 
            constant grouping in which you want to obtain the last date: 0, 1, 2, 3 or 5 (see constants list), the second is the modifier 
            of the base date from today: 0, 1, 2, 3 or 5 (see constants list) and the third is the amount to change the modifier. 
  Examples: LAST_DAY_OF(1;0;-1) - last day of the current month (1) from last year (0;-1);

New date

Create an object from a day, month and year.

   Syntax: NEW_DATE(<expression_day>;<expression_month>;<expression_year>). Need three parameters, the first is a valid day number (1 to
           31), the second for a month (1 to 12) and the third is a year. The date is created from year to day, therefore, the day must be
           valid for the month and the year (considering 29/02, year must be bissextile). 
 Examples: NEW_DATE(1;4;DATE_PART("Order Date";0)) - create the first day of april from the year determined by the current row 
           "Order Date" column;

Num of days

Return the number of days between two dates.

   Syntax: NUM_OF_DAYS(<expression_date1>;<expression_date2>). Need two date data type parameters. 
 Examples: NUM_OF_DAYS(FIRST_DAY_OF(0;"Order Date");"Order Date") - returns the number of days between the first day of year based on
           the "Order Date" column to the current day of the "Order Date" column,
           NUM_OF_DAYS(FIRST_DAY_OF(1;"Pay Date"); LAST_DAY_OF(1;"Pay Date")) - returns the number of days of the month of the "Pay Date"
           column;

Num of months

Return the number of months between two dates.

   Syntax: NUM_OF_MONTHS(<expression_date1>;<expression_date2>). Need two date data type parameters. 
 Examples: NUM_OF_MONTHS(FIRST_DAY_OF(0;"Date");"Date") - Return the number of months between the first day of the year of the "Date"
           column and the current month of the "Date" column;

Previous date

Return the closest exact previous date given a day and month.

   Syntax: PREVIOUS_DATE(<expression_day>;<expression_month>). Need two parameters, the first is the day of month and the second the
           month. 
 Examples: PREVIOUS_DATE(1;4) - return the closest 01/04, in other words, until "01/04/current year", returns "01/04/last year",
           after that returns "01/04/current year".

Special variables

Special variables can be use with the expressions.

Important: The variables below must be enclosed in double quotation marks

   "@count" - Total number of lines returned in a table. 

   @row - current line number.

   @sum[Column] - Sum of indicated column.

   "@avg[Column]" - Avg of indicated column like @sum[Column] / @count

   @prev[column] - Previous row column value

   @accumCol[column] - Current accumulated column value. Useful to calculate Variable Avg ("@accumCol[column]" / "@row")