Functions of the Formula Editor

Once you access the function editor you can start defining your custom function column. The most important things to know about the function editor are, 1. With the 3 dots you can add elements in front of the chosen element or cut/copy the branch (as soon as you copied something a paste button will appear at the open ends of the formula). 2. Change the selected elements with a click on the existing field. 3. Add new values to open endpoints of your formula until it is complete. 4. Define the data type of your output. 5. Enrich the column with the unit of the result. 6. Save and go back to the dataset editor.

Overview of the function editor

The most commonly used functions

Math

Divide (in the timescale version) will perform a division with remainder if the dividend is an integer or long and a normal division if the dividend is of type double. If you want an integer divided without remainder cast it to double. Modulo is only available for values of type integer and long.

Statistics

The functions of the category statistics are the same as the ones you can use for the aggregations of your dataset in the dataset editor. This has two implications. 1. If you use a statistics function inside one of your columns then you will have to group/aggregate or not show the other columns of your data set. 2. If you use a statistic function you also need to apply a statistics function on events in different branches of your formula.

Currently, nested aggregation is not supported, which means you cannot use aggregate inside of function, and then use aggregate in dataset column again. This will result in error showing "Function calls cannot be nested".

Cast

With casts, you can change the data type of your events. For example, you can cast your timestamp to long, then the timestamp will be displayed as a number instead of a readable date or you can cast an integer to a boolean where 0 will be cast to false and every other number to true.

Text Operation

• Concat joins two text/number columns into one text column.

• Lower/Upper changes all character to lower/upper case.

• Char Length returns the length of the string.

• Md5 returns the MD5 hash of the string.

• Octect Length returns the number of bytes needed to specify the string.

Time

• Extract, pick a single component of your datetime (e.g. minute, day, or year), based on the time zone "Europe/Vienna".

• Truncate, floors of the datetime up to the specified unit. E.g. truncating to an hour will transform "2020-01-01 09:55:02" to "2020-01-01 09:00:00". Truncate is also based on the "Europe/Vienna" time zone.

• Format, return your timestamp in a custom format. See Datetime Formatting (Table 9-21)

  for details with the timescale version and Datetime Formatting (section Date/Time Conversions) for Crate.

• Now, returns the current UTC timestamp.

Conditional

• Greatest/Least takes the highest/lowest value of the supplied events/constants per row.

• Coalesce takes the first not null value of the supplied events/constant per row.

• If row-wise if-else statement.

• Switch Case row-wise switch case.

Example of Greater and If statement

This is how the if-statement would look like in the function editor.

Example of Coalesce statement

This is how the coalesce-statement would look like in the function editor.

Event

With the events, you can find all the events you have access to in the dataset editor (excluding dimensions).

Dynamic

Deprecated.

Output Settings

In the output settings, you define the data type and the unit for the result of the function. In case the result is of type double also the number of decimals. The definition of the data type is not actually a cast but it is used to determine how to display the result and what kind of filters, aggregations, and groupings should be available. The output type time is used for differences of timestamp, durations so to speak.