Expression editor

An expression is a calculation that produces a value or data set. It can be a combination of functions, fields, and mathematical operators. Expressions are used to process data in the app in order to produce a result that can be seen in a visualization.

Click Open Expression editor () in the properties panel when editing a visualization or a sheet.

The symbol indicates an expression field, for example titles, subtitles, footnotes, dimensions, and measures.

Type directly into the expression editor, or use the expression editor panel to build expressions.

In the expression editor, you can enter an expression by typing in the expression field. When you type the name of a function in the expression, a tooltip appears that provides information to help you enter the function correctly, including argument names and qualifiers.

See:

• Working with the expression editor

• Set analysis

Fields

Select a field from a specific table and a common aggregation function to generate some common expressions to be inserted into the expression editor. You can also select system fields.

To learn more about aggregation functions, see Basic aggregation functions, Counter aggregation functions and Statistical aggregation functions.

To learn more about system fields, see System fields.

Clauses and set expressions

Some aggregation functions might require a Distinct clause or a Total clause. Use the check box for each clause to insert it in the aggregation function. Each clause can be used independently.

Use the Set Expression check box to insert your current selection as a set expression in your aggregation. The current selection inserted will always be based on the default state. If you want to create an expression based on an alternate state use the Set Expressions section instead.

To learn more about how to use these set expressions, see Set modifiers.

To learn more about alternate states see Using alternate states for comparative analysis.

Expression generator

When using the expression editor for creating measure expressions, you can generate expressions using natural language. Expand the expression generator, select a language, and type a description of the desired expression. In your description, include the names of the fields or master items, along with the basic aggregations and filters you need to use.

Under Expression preview, you can see the output expression that has been generated. Click Insert to add the expression.

Use the expression generator to create measure expressions for charts and master measures. You can also use it to create expressions to control coloring in your visualization.

Limitations when using the expression generator

The expression generator has the following limitations:

• If custom business logic has been applied to the app, it will influence the expressions generated by your questions. Specifically, if a default calendar period has been defined for a measure within the app's business logic (under Behaviors), generic questions without references to temporal (date) information will automatically generate an expression with the autocalendar applied.

  For example, asking for total sales might generate an expression such as the following:

  ={<[OrderDate.autoCalendar.QuartersAgo]={0}>} sum([Sales])

  This is an example of a dynamic expression, but only appears when the app uses a relative calendar period as the default calendar period. Other generated expressions referencing autocalendar will be static, so do not update as periods naturally change over time.

• Questions referencing temporal (date) fields will generate expressions in which specific date values are represented as static numeric values. Asking a question with direct reference to date information will result in dates represented in this way, regardless of whether a default calendar period has been defined for the measure. These numeric date values do not adjust as periods naturally change over time.

• The following question types are not supported in the expression generator:

  • Questions using master measures with aggregations applied to them (in other words, aggregations applied to aggregations)

  • Questions referencing the top or bottom dimension values for a measure (for example, total sales for top 3 product names)

  • Questions using the 'trend' keyword (for example, Total sales trend for 2023)

  • Questions intending to generate period over period expressions (for example, compare total sales between 2022 and 2023)

• The expression generator supports natural language questions in the following languages:

  • German

  • English

  • Spanish

  • French

  • Italian

  • Dutch

  • Polish

  • Portuguese

  • Russian

  • Swedish

Functions

Select a function from a specific category to insert it into the expression editor. You can use functions to generate more complex expressions. A small syntax template shows you how each function is used.

See: Script and chart functions

Set expressions

Select whether you want to insert the current selection or a bookmark as a set expression. If available, you can choose which alternate state on which to base the current selection or bookmark.

Variables

Select variables and insert them in your expressions. If available, a definition and the value of each variable is shown.

See: Using variables in expressions