Formula Editor

The Formula Editor is a text editor that activates, when you click the Formula field or select the Custom option.

To open the Formula Editor in full-screen mode, click the button on the right side of the field.

You can use the Formula Editor, when configuring:

• Dimensions
• Measures
• Variables
• Colors
• Tab display condition
• Measure display condition
• Dimension display condition
• Data input method when running a script from a dashboard

Formulas support ClickHouse functions. You can review them on the official ClickHouse website.

Formula Editor Tabs

Tables and Columns

The Tables and Columns tab displays tables added to the workspace. To show a table, click Show table and select the required one from the dropdown list.

You can find the required table using the search field.

The column list appears after selecting a table. Multiple tables and their columns can be displayed simultaneously. To expand a column list of a table, click the arrow icon to the left of its name.

To add a column to the formula, click it. To find a specific column, select the table and enter the column name in the search field.

While writing a formula, you can use autocomplete for table and column names from the list on the right. To trigger autocomplete, start typing the name of a column or table and press Ctrl+Space. If multiple options are available, they appear below the formula as a list. If only one match exists, it is inserted automatically.

After completing the formula, click Save.

Variables

The Variables tab contains system variables, dashboard variables, and input values. To expand a variable category, click the arrow icon to the left of its name.

To locate a specific variable, use the search field.

To add a variable to the formula, click it.

System Variables

System variables retrieve current values of specific system parameters.

To access a system variable, the user must have the Users access privilege with the R operation.

The following system variables are available:

• currentEmployeeId
• currentEmployeeEmail

The currentEmployeeId and currentEmployeeEmail variables pass user information to a script during dashboard action execution. They can also filter data per user by applying a widget filter using the system variable. Each user then sees only their personal data.

The currentEmployeeId variable contains the identifier of the current dashboard user. Using this variable avoids querying the employee table to resolve an ID from a login.

The currentEmployeeEmail variable contains the email of the current dashboard user.

Dimensions

The Dimensions tab displays global workspace indicator groups and dashboard dimensions. The global workspace indicator group *Common always appears first in the list. To expand a dimension list of agroup, click the arrow icon to the left of the group name.

To add a dimension, click its name. The dimension is inserted in the format link: "Group_Name"."Dimension_Name".

Measures

The Measures tab displays global workspace indicator groups and dashboard measures. The global workspace indicator group Common always appears first in the list. To expand a measure list of a group, click the arrow icon to the left of the group name.

A measure is inserted in the format link: "Group_Name"."Measure_Name".

Colors

The Colors tab appears only when configuring a color, for example, for a display rule.

Additional Features

Finding and Replacing Values

The editor includes a built-in search function. To find a value in the text, select Find from the context menu. The search field appears.

To replace found values, select Find and Replace from the menu and click the arrows to the right of the Replace field. One arrow replaces the selected match; two arrows replace all matches.

Keyboard Shortcuts

The Formula Editor supports the following keyboard shortcuts:

• Ctrl+A — select all
• Ctrl+X — cut
• Ctrl+C — copy
• Ctrl+V — paste
• Ctrl+Z — undo
• Ctrl+Shift+Z — redo
• Tab — indent right
• Shift+Tab — indent left
• Ctrl+Shift+X — uppercase
• Ctrl+Shift+Y — lowercase
• Ctrl+/ — single-line comment
• Ctrl+Shift+/ — multi-line comment
• Ctrl+D — duplicate lines
• Ctrl+Shift+D — delete line
• Ctrl+F — find
• Ctrl+H — find and replace
• Ctrl+Shift+F — format
• Ctrl+Space — autocomplete
• Ctrl+S — save and close

Formula Usage Notes

Consider the following when working with formulas:

• If a formula uses the multiIf function with columns from different tables as parameters, data may display incorrectly. For example, this occurs when using the function to switch measure display on a chart based on a set of conditions.
  In such cases, use a measure display condition instead of the function. Define a separate display condition for each measure in the widget. This approach yields accurate data without requiring measures to conform to a unified format, as the function demands.
• Do not enclose table and column names in backticks in formulas, as this may cause calculation errors. Use double quotes " instead. Enclose string literals in single quotes '.
• Operavix allows using variables as column names in indicator formulas via the Column list variable. Dynamically substituted columns referenced by such a variable may have different data types, which can cause formula errors. Prevent these errors in two ways:
  • Ensure column data types remain consistent for all columns whose names are used in variables (within indicator formulas)
  • Define the indicator’s data type dependency on the variable in the YAML editor, as in the example below, where the dbDataType key receives the value of a variable storing the required data type

dimensionsAndMeasures:
  - name: Formula
    type: DIMENSION
    value:
      mode: FORMULA
      formula: '!"inputs"."Input value"."priority"'
    dbDataType: '"inputs"."Input value 1"'

• Prefixing a variable with an exclamation mark (!) wraps its value in double quotes. Usage example:
  1. Create and configure a Column list variable in the dashboard.
  2. Add a Table widget to the dashboard. Use "Table_Name".!"inputs"."Column list" as the dimension formula.
  3. Add a Parameter widget to the dashboard and include the Column list variable.
  4. Publish the dashboard. The column used in the table dimension now changes dynamically, when you modify the Column list variable via the Parameter widget.