Senseforce 2.0 Manual
  • Welcome to Senseforce 2.0
  • SF MQTT API
  • SF Edge
    • Edge Package Overview
    • Edge Installation
      • Using the SF Edge Service installer in command shell
      • Headless SF Edge Servie execution
    • Edge Configuration
      • Which Plugins to load (appsettings.xml)
      • Edge Data Routing (EventDefinition.xml)
      • OTA configuration (otasettings.xml)
      • Edge Logging
      • Input plugins
        • SQL Plugin
        • SQLite Plugin
        • OPC UA Plugin
          • OPC UA server browser
        • SIEMENS S7/SINUMERIK Plugin
          • Creating connection definition file
        • S7 Plugin
        • REST Plugin
        • TCP Listener Plugin
        • XML File Parsing Plugin
        • Heidenhain Plugin
      • Output Plugins
        • MQTT Plugin
        • SQLite Plugin
    • Edge Compute plugins
      • Creating compute plugins
      • Using compute plugins
  • SF Edge Asset Management
    • Edge version repository
  • SF Platform
    • Navigation
      • Overview Screen
    • Dataset Builder
      • Filters, Aggregations and Groupings
      • Functions of the Formula Editor
      • Additional Settings
      • Special Events
      • FAQ and Troubleshooting
    • Script Editor
      • Details of the Script Editor
      • Installed Packages
      • Working with Timestamps
        • Working with Timestamps in Python
        • Working with Timestamps in R
      • FAQ and Troubleshooting
    • Widgets
      • Text
      • Headline
      • Image
      • Progress Bar & Gauge Chart
      • Table
      • Map
      • Line, Bar, Scatter and Area Chart
      • Pie Chart
      • Gantt Chart
      • Histogram
      • Overview
      • Log
      • Default colors and color conditions
      • FAQ and Troubleshooting
    • Dashboards
      • Dashboard Filters
      • Favorites
      • Data Drilldown & Widget Interaction
      • Editing multiple dashboard widgets
      • Time Zones
      • Synchronized Zooming
      • Sharable Dashboard URL
      • Multi-chart layout options
      • Default sort for table widgets
      • Releases
      • Reporting
      • FAQ and Troubleshooting
      • Applying zoom to global timestamp filter
      • Optimise the layout for different devices
    • Machine Master Data
      • Dimensions
      • Instances
      • Things
      • Use Case
      • FAQ and Troubleshooting
    • Automation
      • Trigger
      • Scheduling overview
      • Actions
      • Test your Automation
      • Zapier integration (necessary internal steps)
      • Zapier integration
      • Subscriptions
      • FAQ and Troubleshooting
    • Event Schema Management
      • Importing a Event Schema
      • FAQ and Troubleshooting
    • Virtual Events
      • Creating a Virtual Event
      • Scheduling Overview
      • Permissions and Data Access
      • FAQ and Troubleshooting
    • Details modal for elements
    • Copy / Duplicate elements
    • Whitelabeling
    • Edge Device Management
    • Element History
    • Public API
      • Get your access token
      • Endpoints
      • Debugging scripts
      • FAQ and Troubleshooting
    • User & Group Management
      • FAQ and Troubleshooting
    • Active Directory & SSO Setup
Powered by GitBook
On this page
  • The most commonly used functions
  • Math
  • Statistics
  • Cast
  • Text Operation
  • Time
  • Conditional
  • Event
  • Dynamic
  • Output Settings

Was this helpful?

  1. SF Platform
  2. Dataset Builder

Functions of the Formula Editor

PreviousFilters, Aggregations and GroupingsNextAdditional Settings

Last updated 2 years ago

Was this helpful?

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.

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.

  • 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.

  • 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.

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.

Md5 returns the hash of the string.

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

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

MD5
Datetime Formatting
Datetime Formatting
Overview of the function editor
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.