Formula syntax

This article contains the following subsections:

• Basic syntax:
  • Addressing dataset fields
  • Constants
  • Operators
  • Formula formatting
  • Comments
  • Type conversion
• Functions for data analysis:
  • Aggregation functions
    • AVG
    • COUNT
    • SUM
  • Window functions
    • RANK
    • MCOUNT
  • Logical functions
    • CASE
    • IF
    • IFNULL
    • ISNULL
    • ZN
  • String functions
    • CONCAT
    • REPLACE
    • TRIM
  • Text markup functions

Basic syntax

Visualization formula syntax is similar to SQL.

You can describe simple expressions with usual arithmetical operations:

([Sales] - [Profit]) / 10

[OrderDate] - #2022-01-24#

More complex expressions use functions to perform calculations, aggregation, and conversion of data:

CONCAT(SUM([Total]) / [UnitCost], ' %')

AVG([WorkHours])

DATETRUNC([datetime], 'month')

Addressing dataset fields

Syntax of addressing dataset fields is similar to Transact-sql. The difference is that you need to put the field name in square brackets ([]):

[field name]

Constants

You can use constants for the following data types:

• Integer: 42, -32456
• Float: 0.8765, -1.701
• Date: #2022-02-12
• Date and time: #2022-02-12 13:14:15#
• String: "String here"
• Boolean: TRUE, FALSE
• Geopoint: GEOPOINT("[55.7912,37.6872]")
• Geopolygon: GEOPOLYGON("[[[55.75,37.52],[55.75,37.68],[55.65,37.60]],[[55.79,37.60],[55.76,37.57],[55.76,37.63]]]")

Operators

Formulas support the following operator types:

• Arithmetical: +, -, *, /. ([Sales per Order] * [OrderCount]) - [Income]

  ([Profit] / [Price per Unit]) * 100
  

  [City] + ", " + [StateCode]
  

  Note

  Addition (+), deduction (-) and multiplication (*) operators change behavior depending on argument type.

• Exponentiation: ^.

  [mass] * [speed] ^ 2
  

• Remainder: %.

  [Total] % 10 + [Unit cost] % 10
  

• Logical: AND, OR, NOT, IN, LIKE, IS TRUE, IS FALSE, BETWEEN.

• Comparison: =, !=, <, <=, >, >=.

  Comparison operators allow you to create logical chains:

  1 > A > -4 > B != 8
  

Formula formatting

Write formulas in a single line:

CONCAT(SUM([Category Sales]) / [Total Sales], ' %')

... or in multiple lines:

CONCAT(
   SUM([Category Sales]) / [Total Sales],
   ' %'
)

Comments

Use comments to add explanations or to ignore parts of the formula. Visualization syntax supports two types of comments:

• Single-line:

  -- This is a single-line comment
  

• Multi-line:

  /* This is 
  a multi-line comment */
  

Type conversion

You can convert expression values from one type into another:

FLOAT([String_With_Numbers])

DATETIME(STR([OrderDate]) + "-" + STR([OrderTime]))

GEOPOINT([Latitude],[Longitude])

Functions for data analysis

Use functions to perform calculations, logical operations, aggregation, and conversion of data. This subsection contains most commonly used data analysis functions.

For a complete list of supported functions, see all functions reference

Aggregation functions

Use aggregation functions to calculate resulting values.

For more information on this type of functions, see aggregation functions overview.

AVG

Returns the average of the values in a set. It ignores NULL values.

AVG([Total]) * COUNTD([CustomerID])

For more information, see AVG.

COUNT

Returns the number of records from a table column.

SELECT COUNT([City])

For more information, see COUNT.

SUM

SUM_IF([Sales], [Category] = "Books")

Window functions

Use window functions to aggregate values from multiple strings without merging them into one.

For more information on this type of functions, see window functions overview.

RANK

Returns the rank of the current row if ordered by the given argument.

RANK([Profit] TOTAL)

For more information, see RANK.

MCOUNT

Returns the moving count of (non-NULL) values in a fixed-size window defined by the sort order and arguments.

MCOUNT([Profit], 3)

For more information, see MCOUNT.

Logical functions

Use logical functions for calculation branching.

For more information on this type of functions, see logical functions overview.

CASE

Compares an expression with sequence of value_1, value_2 and further, and returns the result of the first match. If no matches found, returns default_result:

CASE( 
   [expression],
   value_1, result_1,
   value_2, result_2,
   default_result
) 

For more information, see CASE.

IF

Sequentially checks logical expressions condition_1, condition 2 and further, and returns the result of the condition that shows TRUE. If all the conditions show FALSE, returns default_result:

IF(
   condition_1, result_1,
   condition_2, result_2,
   default_result
) 

For more information, see IF.

IFNULL

Returns check_value if the value isn't NULL, otherwise returns alt_value:

IFNULL(check_value, alt_value)

For more information, see IFNULL.

ISNULL

Returns TRUE if expression is NULL, otherwise returns FALSE:

ISNULL(expression)

For more information, see ISNULL.

ZN

Returns the value of expression if it isn't NULL, otherwise returns 0 (a number):

ZN(expression)

For more information, see ZN.

String functions

Use string functions to process character data.

You can set strings with single and double quotes. You can use one type of quotes inside another:

CONCAT('"', [Product Name], '"')

Use special characters to perform string data conversions in formulas:

REPLACE([OfficeAddress], "\n", " ")

For more information on this type of functions, see string functions overview.

CONCAT

Joins two or more strings into one.

CONCAT([Total Sales], "$")

For more information, see CONCAT.

Replace

Replaces all the instances of a specified string value with a provided string value.

REPLACE([OrderID], "2022", [Month])

For more information, see REPLACE.

TRIM

Removes leading or trailing whitespaces from the string.

TRIM(" String ")

For more information, see TRIM.

Text markup functions

Use markup functions to create text markup:

MARKUP(BOLD("This"), " ", ITALIC("is"), " ", URL("https://example.com/", [LinkName]))

For more information on this type of functions, see text markup functions overview.