# Formula syntax

## 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')


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],
' %'
)


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])


#### COUNT

Returns the number of records from a table column.

SELECT COUNT([City])


#### 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)


#### MCOUNT

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

MCOUNT([Profit], 3)


### 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
)


#### 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
)


#### IFNULL

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

IFNULL(check_value, alt_value)


#### ISNULL

Returns TRUE if expression is NULL, otherwise returns FALSE:

ISNULL(expression)


#### ZN

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

ZN(expression)


### 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], "\$")


#### Replace

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

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


#### TRIM

Removes leading or trailing whitespaces from the string.

TRIM(" String ")


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