Calculated Fields

Introduction

The calculated fields feature allows you to make calculations on numeric and textual fields inside the report itself, so there is no obligation to export the report to Excel. In addition, there is full support for filtering the fields according to the calculation results.

This feature can also be used in schedule reports, saved as favorites, etc.

Basic points

There is a differentiation between textual to numeric fields. The user has to select “numeric” or “textual” in the calculated fields toggle, depending on the required calculation. As a result, different fields will be available for each type of calculation engine. Only system parameters which return numeric values will be available in the numeric engine, while in the textual engine all fields will be available (but will be considered as strings).
The engine is case-insensitive when using functions names

Supported Mathematical Functions

Basic mathematical functions

➕
➖
✖
➗

Advanced numerical mathematical functions

In numerical formulas, line breaks (Enter) are not permitted, as otherwise the calculation is not
performed and we are left with 0 as a result.

Function	Explanation	Examples
MIN/MAX	The function will display the smallest/largest number between two or more fields or numbers	Min(unit_id; 1000) Max(unit_id; 13.5)
AVG	The function will display the average between two or more fields or numbers	AVG(a1; a2; a3; ...)
ABS	The ABS function returns the absolute value of the given number	ABS(-10)
IF	The “IF” function is a decision-making condition function between numerical fields. Syntax: IF(a; b; c) -> if a is true, return b, else return c. The following boolean operations are supported in numeric fields: "<", ">", "==", "!=" 0 == 0 -> True 0 != 0 -> False	IF(unit_id > 1000; 10; 9)

Advanced Textual functions

All text/string in textual fields must be written inside single quotes, i.e., ‘Text’ (not including function names).

You must use a semicolon (;) sign to separate the clauses in the equation instead of a regular comma.

Textual fields are case sensitive. For example, CONTAINS(‘OFFGRID’ ; ‘off’) returns false, but CONTAINS(‘OFFGRID’ ; ‘OFF) returns true.

To concatenate several And/or conditions, add parentheses for each condition, as in the following example: IF(('unit_is_stationary' = 'Yes')&&('unit_is_stationary' = 'Yes'); 'OK';'Not OK')

Single quotes are supported inside a value only by prefixing them with the escape string(\) i.e. a formula
can look like this: 'unit_name' + ' Te\'st' and the result will be unit name Test

Function	Explanation	Examples
SWAP	The function will convert a numeric field to a text field	Swap(’real_time_status’ ; ‘0:Down’ ; ’999:Off Grid’)
IF	The “IF” function is a decision-making condition function between textual fields. Syntax: IF(‘a’ ; ’b’ ; ‘c’) -> if “a” is true then “b” else “c”. We support the following Boolean operations in text fields: ‘a’ = ‘a’ -> True ‘a’ != ‘a’ -> False	IF('unit_name' = ‘arkadi test’ ; ‘true’ ; ‘false’)
CONTAINS	The function CONTAINS('String' ; 'Substring') returns True if 'String' contains 'Substring'. Most of the times will be sued inside an IF function, to check a specific substring in a system field.	CONTAINS('site_layout' ; 'OFFGRID') -> True/ False IF(CONTAINS('site_layout' ; 'OFFGRID') ; 'off-grid' ; 'on-grid') -> 'off-grid'/ 'on-grid'
NUM	The function can be used to perform numeric calculations in a textual field. Most of the times will be used inside a function, to perform numeric comparison and return a string. Note that the entire expression inside the NUM function should be inside single quotes. In addition, the entire expression inside the NUM function should follow the syntax of numeric fields, so the following boolean operations are supported inside the function: NUM('0 == 0') ->True NUM('0 != 0') -> False	NUM('DG1FuelConsumed > 0') -> True/ False IF(NUM('DG1FuelConsumed > 0') ; 'Fuel Consumed' ; 'No Fuel Consumed') -> 'Fuel Consumed'/ 'No Fuel Consumed'
+	Simple concatenation function	'Danielle Unit' + 'Test' -> 'Danielle Unit Test'

Calculation Field Creation

Click on the “+Add Field” and choose the “Calculated Field”. This action will open the calculation fields wizard.

Give the calculated field a name
Choose the relevant field then click one of the mathematical symbols and choose another field. You will see all the formulas in the “formula field”. For example, you can multiply the “Engine Hours“ by the “Current”. In the end, you need to click “Save”

The calculated field available at the reports' output, as any other field.

Additional notes:

As with any standard report field, the calculated field name can be changed, and filtering functionality is fully supported!

Regular Expression

Galooli reports system allows you to perform a variety of advanced filtering manipulations without using third-party solutions such as Excel.

A regular expression (Regex) is a sequence of characters that specifies a search pattern of textual information. Usually, such patterns are used in order to filter text data before its presentation. Regular expression allows a combination of several filters together for example “and”, “or” filters.

Galooli’s Pro solution allow you to perform a variety of simple filtering manipulations on the textual data such as “Text Contain”, “Text Not Contain” before you run the report.

These filtering tools facilitate the analysis of the final information obtained from the report and save time so the user does not have to use formulas or tools in third-party solutions such as Excel.

However, sometimes more advanced filtering manipulations are required, and here the Regular expression filtering tool can be very useful.

The use of the tool

A regular expression (Regex) is a sequence of characters that specifies a search pattern of textual information. Usually, such patterns are used in order to filter text data before its presentation. Regular expression allows a combination of several filters together for example “and”, “or” filters.

Find a textual report and choose the Regular expression option
Write the appropriate formula for the required filtering

Limitation

A regular expression can be used in textual fields only.

Examples of common formulas

Expressions	Formula
XX OR XX	(XX\|XX)
XX AND XX	(?=.XX)(?=.XX)
Not XX OR Not XX	^(?!.(XX\|XX)).

XX=the expression

Example of using the tool

Example #1

For example, if your report contains two columns:

“Unit Name” field - Textual field that contains all the organization vehicle models (Mazda, Opel, Ford, Honda, etc.)
“Status” field - Field that presents a variety of statuses of the vehicle (Disconnected, Moving, etc.)

Now your mission is to find only the Mazda OR the Ford modules that their status is disconnected.

In this situation, you should use the Regular expression tool in the following way:

Choose the “Regular expression” in the “Unit Name”
Write the appropriate formula XX|XX. In this case, the XX is the name of the cars model
Choose the “Disconnected” status in the “Status” field
Run the report

You will get only the disconnected MAZDA or FORD models

Example #2

For example, if your report contains one column:

“Unit Name” field - Textual field that contains all the organization sites names (Site1, Site 2, Site3, Site4, etc.) AND the identification number of each site (ABC123, ABC456, ABC789, DEF123, DEF456, DEF789, etc.)

Now your mission is to find only Site1 AND that their identification number contains ABC letters. It is important to emphasize that using AND formula requires that all the information appears in the same field (Site1 ABC123 for example)