| Table of Contents |
|---|
Calculated Fields
Introduction
The A calculated fields field feature allows you to make calculations on numeric and textual fields inside the report itself, so there is no obligation 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. In addition, there is full support for filtering the fields according to the calculation resultsAdditionally, 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.
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
...
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
...
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") |
| Counts how many of the listed fields are equal to the value. It is possible to enter a textual, numeric, or date value. Example: COUNTEQUALS (“Yes”,[Tenant_1.Configured],[Tenant_2.Configured],[Tenant_3.Configured]) |
| 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) |
| Converts a time span object to a numeric value which presents the number of hours. Example: TOHOURS([EndTime] - [StartTime]) = "01.01:00:00" returns 25 |
| Converts a time span object to a numeric value which presents the number of days. Example: TODAYS([EndTime]-[StartTime]= "1.12:00:00" returns 1.5 |
Calculation Field Creation
Click on the “+Add Field” Field” and choose the “Calculated Field”“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.
...
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!
...