Prerequisites: You must have a functional administrator role for formula creation (calculated metrics).

For the creation of your formulas, the Formulas module includes basic operators as well as the ability to add certain functions.

A formula can be created with a single metric, or a set of metrics linked together by operators.

List of available operators

The functions allow more complex evaluations to be carried out. Here is the list of functions that are available.

Purpose : Allows to evaluate the equation passed in the condition parameter. The function returns the values passed in the "trueValue" and "falseValue" parameters according to the result of the evaluation.

Definition : If(condition, trueValue, falseValue)

Purpose : Returns a value X if the condition is valid.

Definition : ifthen([condition], [result if true])

Purpose : Generates a random number greater than or equal to 0.0 and less than 1.0

Definition : rand()

Purpose : Monitor the status of the data-transmitting equipment (Indabox, eWon, etc.)

Definition : bool Watchdog(string [idMetric], int [duration], bool [watchValueChange])

Functioning :

If the metric [idMetric] has not been written within [duration] seconds, the function returns True.

If the [watchValueChange] parameter = true, the function monitors the change in value between two writings.

If the last 2 values written in [idMetric] are equal, the function returns True.

Equation : result = ( [LastRefresh]<[Now]-[Duration] ) OR ( [watchValueChange] AND [PreviousValue]=[CurrentValue])

Purpose : To generate a fixed, absolute date from a character string.

Functioning :

Accepted date formats are :

The formula is invalid if the date is not entered in one of these formats.

Example of a valid expression :

Purpose : Allows returning the value prior to the current value of a metric.

Definition : previousValue("[metric]")

Functioning : Simply replace the parameter [metric] with the desired metric. The function will return the value that the metric had just before its current value.

Example : To determine the variation in the value of a metric "temperature" between two readings, take the current value of the metric ("main@temperature") and subtract the previous value of the metric ("previousValue(main@temperature)").

In addition to advanced formulas, you have access to "Indaba" operators that allow you to customize your calculated metrics to meet more specific needs.

When configuring these Indaba operators, you will need to specify the period you wish to analyze.

To do so, you must use the time operators provided.

Note : Indaba functions cannot be used in a formula where the periodicity is "continuous".

Note : You can use a maximum of 4 Indaba functions in a formula.

In the system of time operators, the term "now" represents the current date. Then you have several methods (or functions) to manipulate the current date, with each function representing a value :

For example :

Here, the indicated period will be the start of the current day (start date) and the end of the current day (end date).

Purpose : Returns the minimum value of a metric for a given period.

Definition : idb.min("[metric]", [start date], [end date])

Functioning : Replace the [metric] parameter with the metric for which you want to know the minimum value during a period. To define this period, replace the [start date] and [end date] parameters with the aforementioned temporal operators.

Example : Suppose you want to know the minimum humidity level in one of your installations during the current month.

Your humidity level is represented by the metric "humidity_level".

To indicate the desired period, use the temporal operators. Here, we want the data for the current month, so we will use "now.startOfMonth" (start of the month) to indicate the start of the period and "now.endOfMonth" (end of the month) to indicate the end of the period.

This results in the following expression :

Purpose : Returns the maximum value of a metric for a given period.

Definition : idb.max("[metric]", [start date], [end date])

Functioning : Same operation as for the Idb.min operator.

Replace the [metric] parameter with the metric for which you want to know the maximum value during a period. To define this period, replace the [start date] and [end date] parameters with the time operators mentionned before.

Example : Suppose you want to know the maximum humidity level in one of your installations during the current month.

Your humidity level is represented by the metric "humidity_level".

To indicate the desired period, use the time operators. Here, we want the data for the current month, so we will use "now.startOfMonth" (start of the month) to indicate the start of the period and "now.endOfMonth" (end of the month) to indicate the end of the period.

This results in the following expression:

Purpose : Returns the average value of a metric for a given period.

Definition : idb.max("[metric]", [start date], [end date])

Functioning : Replace the [metric] parameter with the metric for which you want to know the average value during a period. To define this period, replace the [start date] and [end date] parameters with the time operators mentionned before.

Example : Suppose you want to know the average temperature of a compressor during the current week.

Your temperature is represented by the metric "temperature". To indicate the desired period, use the time operators. Here, we want the data for the current week, so we will use "now.startOfWeek" (start of the week) to indicate the start of the period and "now.endOfWeek" (end of the week) to indicate the end of the period.

This results in the following expression:

Purpose : Returns the sum of the values of a metric for a given period.

Definition : idb.sum("[metric]", [start date], [end date])

Functioning : Replace the [metric] parameter with the metric for which you want to know the sum of the values reported during a period. To define this period, replace the [start date] and [end date] parameters with the operators mentionned before.

Example : Suppose you want to know the total water consumption of a compressor during the current month.

Your water consumption is represented by the metric "water_consumption". To indicate the desired period, use the time operators. Here, we want the data for the current month, so we will use "now.startOfMonth" (start of the month) to indicate the start of the period and "now.endOfMonth" (end of the month) to indicate the end of the period.

This results in the following expression :

Purpose : Determines how many times a value has been reported for a given metric.

Definition : idb.count("[metric]", [start date], [end date])

Functioning : Replace the [metric] parameter with the metric for which you want to know the number of reported values during a given period. To define this period, replace the [start date] and [end date] parameters with the time operators mentioned before.

Example : You want to check how often your equipment reports values during the current day.

Purpose : Returns the last value before a given date.

Definition : idb.firstValueBefore("[metric]", [date])

Functioning : Replace the [metric] parameter with the metric for which you want to know the last value before a given date. To define this date, replace the [date] parameter using the time operators mentionned before.

Example : You want to know the last value reported by a piece of equipment before the start of the current day.

Purpose : Returns the first value after a given date.

Definition : idb.firstValueAfter("[metric]", [date])

Functioning : Replace the [metric] parameter with the metric for which you want to know the first value after a given date. To define this date, replace the [date] parameter using the time operators mentionned before.

Example : You want to know the first value reported by a piece of equipment within the current day.

Purpose : Returns the median value of a data range.

Definition : idb.median("[metric]", [start date], [end date])

Operation : Replace the parameter [metric] with the metric for which you want to know the median value over a given period. To define this period, replace the parameters [start date] and [end date] using the temporal operators mentioned above.

Example : You want to know the median temperature value of a device for the current day.

Purpose : Returns the standard deviation of a data range.

Definition : idb.stdDev("[metric]", [start date], [end date])

Operation : Replace the parameter [metric] with the metric for which you want to know the standard deviation over a given period. To define this period, replace the parameters [start date] and [end date] using the temporal operators mentioned above.

Purpose: Returns the first value of a data range.

Definition: idb.first("[metric]", [start date], [end date])

Operation: Replace the parameter [metric] with the metric for which you want to know the first value over a given period. To define this period, replace the parameters [start date] and [end date] using the temporal operators mentioned above.

Purpose: Returns the last value of a data range.

Definition: idb.last("[metric]", [start date], [end date])

Operation: Replace the parameter [metric] with the metric for which you want to know the last value over a given period. To define this period, replace the parameters [start date] and [end date] using the temporal operators mentioned above.

In addition to the previously mentioned functions, the formula engine allows for other types of calculations.

Various mathematical methods and constants can be used in formulas.

For example, it is possible to return the absolute value of a number by entering : Abs(Double)

Or, to return the logarithm of a specified number :

Log(Double)

You also have the possibility to include mathematical constants.

Example :

Note : The list of operators available in the formula creation screen is not comprehensive. For an exhaustive list of the possibilities, click on "see the documentation."

In addition to the time operators seen previously :

You can customize the dates used in your formulas using the following functions :

This list is not exhaustive, and you can find all the possibilities available by clicking here.

In practice, if we use the "now.startOfDay" operator, for example, it will represent the beginning of the current day.

But let's say we want to have yesterday's date. We can complete our time operator with the AddDays function :

now.StartOfDay.AddDays(-1).

By adding .AddDays(-1), we indicate that we want the current day minus one day.

You can thus add/remove days, hours, months, years... to your date.

Example : We want to know the average temperature of an installation over the past 3 years (excluding the current year, which we will call n).

You need to set the period :

You have the option to include temporal operators outside of Indaba functions.

This feature offers various possible use cases :

It is possible to compare dates using comparison operators (>, >=, <, <=, =).

For example :

Here, we compare the current date (with the temporal operator "now") and a fixed date.

A subtraction of dates (date - date) returns an object of type "TimeSpan," which represents a duration.

This object can be utilized by combining it with other functions.

For example, it is possible to calculate the number of hours that have elapsed since the beginning of the month :

Here, (now - now.StartOfMonth) represents the duration between the current date and the beginning of the month.

We then multiply this duration by the function TotalHours to return the number of hours passed in the month.

For more use cases, click here.

Note : It is not possible to add two dates. To add a duration to a date, you must use the .Add[time unit] functions seen previously.