Calculated Fields
Introduction
A calculated field feature allows you to perform calculations between different fields that you have within the report you have created, so you are not required to export the report to Excel. Additionally, the fields can be filtered based on the results of the calculation.
This feature can also be used in schedule reports, saved as favorites, etc.
Full Available Functions List
Function | Examples |
|---|---|
If | Check if 'Condition' is met. Returns 'ValueIfTrue' is it does, otherwise returns 'ValueIfFalse'. Example: IF([Status] = “Disconnected”, “Disconnected”, “Connected”) |
MIN | Returns the smallest number in a set of numeric expressions. Can be used to compare multiple values. Example: MIN([Tenant 1 Total [KWH]], [Tenant 2 Total [KWH]], [Tenant 3 Total [KWH]]) |
| Returns the largest number in a set of values. Can be used to compare multiple values. Example: MAX([Tenant 1 Total [KWH]], [Tenant 2 Total [KWH]], [Tenant 3 Total [KWH]]) |
AVERAGE | Returns the average of the arguments. Can get multiple values as arguments. Example: AVG([Site_Global.Grid_V1 [V]], [Site_Global.Grid_V2 [V]], [Site_Global.Grid_V3 [V]]) |
| Returns the absolute value of a number. Example: ABS([Site_Global.Battery_Power [W]] |
| Returns true if "String" contains "Substring", and false otherwise. Example: IF(CONTAINS([site layout], "OFFGRID"), "off-grid", "on-grid") |
| Check whether all arguments are true, and return true/false respectively. Can get multiple values as arguments. Example: AND([Smart_Battery_1.Disconnected] = "No", [Smart_Battery_2.Disconnected] = "No") |
OR | Check whether any of arguments are true, and return true/false respectively. Can get multiple values as arguments. Example: OR([Smart_Battery_1.Disconnected] = "No", [Smart_Battery_2.Disconnected] = "No") |
+ | Add to numeric expressions, or concatenate textual expressions. |
- | Redact from numeric expressions. Arguments can only be numeric expressions. |
\ | Divides numeric expressions. Arguments can only be numeric expressions. |
* | Multiplies numeric expressions. Arguments can only be numeric expressions. |
^ | Exponentiation of numeric expressions. Arguments can only be numeric expressions. |
= | Check if the expressions are equal. Can be used to compare numeric, text or dates expressions. |
! | Changes False expressions to True, and True expressions to False. Example: [Unit Id] != 8000000 |
<= | Check if A is smaller than or equals to B. Can be used to compare numeric or dates expressions. |
>= | Check if A is greater than or equals to B. Can be used to compare numeric or dates expressions. |
< | Check if A is smaller than B. Can be used to compare numeric or dates expressions. |
> | Check if A is greater than B. Can be used to compare numeric or dates expressions. |
NOW | Returns the current date and time (organization format), according to time offset defined for the cluster. |
ADD HOURS | Adds the 'Number' of hours to the given 'DateTime'. Example: ADDHOURS(NOW(), 3 ) |
ADD DAYS | Adds the 'Number' of days to the given 'Date'. Example: ADDDAYS(NOW(), [Estimated Days to Refuel Tank 1] ) |
REPORT START | Returns the report's start date formatted as a date in the organization format. |
REPORT END | Returns the report's end date formatted as a date in the organization format. |
TO NUMBER | Returns the result as a number. Example: TONUMBER("100") |
TO TEXT | Returns the result as text. Example: TOTEXT(100) |
TO DATE | Converts input to a date result. Example: TODATE(2024, 01, 01, 00, 00, 00) |
Calculation Field Creation
Click on the “+Add Field” and choose the “Calculated Field”. This action will open the calculation fields wizard.
Choose the relevant functions via the “Functions” tab or you may also write down the formula manually. This action will add the function to the right side of the window.
Select the "Report Fields" tab and select the relevant fields from your report on which you would like to perform the calculations. Only fields that are part of the report will appear in the list. Calculated fields will appear with the "calculator" icon and system fields will appear with the "system" icon.
You may add new fields to the report right now without leaving the calculation fields wizard by selecting the "System Fields" tab and adding the new fields.
Give the calculated field a unique name.
Click on “Save” to save the field. The save action will only be enabled if the user entered a valid formula and a valid field name. There will be a variety of popups on the screen with relevant information regarding the calculation.
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)
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 site, and the identification number
Run the report
