# Calculations

Calculations allow us to use much more powerful techniques for data manipulation. Quite often, configuring a chart will require you to apply some aggregations and post-aggregations to your data field. This can be done using in-chart calculations. Technically every data field which is put into an axis or encoding panel (e.g. color) has a calculation. Some dimension calculations can be created using UI aggregation and grouping buttons, but some can only be created using the calculation text box.

## How calculations can be added or edited

New calculations can be added using the "+" sign, or by double-clicking x, y, color, size, or label. The "New Calculation" popup will appear. It utilizes autocomplete and is charged with samples containing almost every available function.

![](https://848134605-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LB5uRCyBpKz1Ivx_jfw%2F-LBa1pMmX-aRXxZpG9Pg%2F-LBa1qy0uzIDvpxEJyD0%2Fadd-calculation.gif?generation=1525351697226230\&alt=media)

You can find the list of available functions and operators below.

## Numerical Calculations

#### +, -, \*, /

Basic math functions.\
\&#xNAN;*Sample: 2\*3 - 1 + 5/2*

#### SUM(numerical expression)

Returns the sum of a series of numbers.\
\&#xNAN;*Sample: SUM(\[Effort])*

#### SUMIF(numerical expression, condition)

Returns the conditional sum of a series of numbers.\
\&#xNAN;*Sample: SUMIF(\[Effort], \[Board] == 'Me')*

#### AVG(numerical expression)

Returns the average of a series of numbers.\
\&#xNAN;*Sample: AVG(\[Effort])*

#### AVGIF(numerical expression, condition)

Returns the conditional average of a series of numbers.\
\&#xNAN;*Sample: AVGIF(\[Effort], \[Board] == 'Super')*

#### MIN(numerical expression)

Returns the minimum of a series of numbers.\
\&#xNAN;*Sample: MIN(\[Effort])*

#### MINIF(numerical expression, condition)

Returns the conditional minimum of a series of numbers.\
\&#xNAN;*Sample: MINIF(\[Effort], \[Board] == 'Me')*

#### MAX(numerical expression)

Returns the maximum of a series of numbers.\
\&#xNAN;*Sample: MAX(\[Effort])*

#### MAXIF(numerical expression, condition)

Returns the conditional maximum of a series of numbers.\
\&#xNAN;*Sample: MAXIF(\[Effort], \[Board] == 'Targetprocess')*

#### COUNT(field)

Returns the count of rows within a series.\
\&#xNAN;*Sample: COUNT(\[Id])*

#### COUNTIF(field, condition)

Returns the conditional count of rows within a series.\
\&#xNAN;*Sample: COUNTIF(\[Id], \[Board] == 'Targetprocess')*

#### COUNT\_DISTINCT(field)

Returns the count of distinct (different) field values within a series.\
\&#xNAN;*Sample: COUNT\_DISTINCT(\[Name])*

#### COUNTIF\_DISTINCT(field, condition)

Returns the conditional count of distinct (different) field values within a series.\
\&#xNAN;*Sample: COUNTIF\_DISTINCT(\[Name], \[Board] == 'Targetprocess')*

#### TOTAL(aggregation(numerical expression))

Returns the total for a given expression across the whole dataset.\
\&#xNAN;*Sample: TOTAL(SUM(\[Effort]))*

#### RUNNING\_SUM(aggregation(numerical expression))

Returns the running sum for a given expression from the first row to the current.\
\&#xNAN;*Sample: RUNNING\_SUM(COUNT(\[Id])) gives cumulative sum by groups defined for chart*

#### RUNNING\_AVG(aggregation(numerical expression))

Returns the running average for a given expression from the first row to the current.\
\&#xNAN;*Sample: RUNNING\_AVG(COUNT(\[Id]))*\
RUNNING\_AVGcalculation\
\&#xNAN;**(prevValue - prevValue / count) + (currentValue / count)**\
for ex., count values is 5, 35, 25\
first value is 5\
for second element, calculation is (5-5/2) + (35/2) = 20\
for third element (take calculated number for second element value), calculation is (20-20/3)+25/3 = 21.67

#### RUNNING\_MIN(aggregation(numerical expression))

Returns the running min for the given expression from the first row to the current.\
\&#xNAN;*Sample: RUNNING\_MIN(SUM(\[Effort]))* 

#### RUNNING\_MAX(aggregation(numerical expression))

Returns the running max for the given expression from the first row to the current.\
\&#xNAN;*Sample: RUNNING\_MAX(SUM(\[Effort]))* 

#### DIFFERENCE(aggregation(numerical expression))

Returns the running difference for the given expression between current row and previous.\
\&#xNAN;*Sample: DIFFERENCE(COUNT(\[Id]))* \
DIFFERENCE calculation is **(current value - previous value)**\
For example, if we have calculation based on cards with MONTH(\[Create Date]) and COUNT(\[Id])

| Month | Count of Cards |
| ----- | -------------- |
| Jan   | 45             |
| Feb   | 19             |
| Mar   | 22             |

RUNNING\_DIFF(COUNT(\[Id])) will give us

| Month | Diff Count of Cards |
| ----- | ------------------- |
| Jan   | 0                   |
| Feb   | -26                 |
| Mar   | 3                   |

####

#### BINS(numerical expression)

Returns the categorical ordered intervals built by income numeric values.\
\&#xNAN;*Sample: BINS(\[Effort])*

#### ROUND(numerical expression, places)

Rounds a number to a certain number of decimal places according to standard rules.\
\&#xNAN;*827 =ROUND(826.645, 0)*\
\&#xNAN;*827 =ROUND(826.645)*\
\&#xNAN;*826.6 =ROUND(826.645, 1)*\
\&#xNAN;*826.65 =ROUND(826.645, 2)*\
\&#xNAN;*826.645 =ROUND(826.645, 3)*\
\&#xNAN;*830 =ROUND(826.645, -1)*\
\&#xNAN;*800 =ROUND(826.645, -2)*

#### ABS(numerical expression)

Returns the absolute value of a number\
\&#xNAN;*Sample: ABS(-3) will return 3*

#### MEDIAN(numerical expression)

Returns the median value in a numeric data set. *Sample: MEDIAN(\[Effort])*

#### PERCENTILE(numerical expression\\, percentile)

Returns the value at a given percentile of a dataset. The 50th percentile, that is setting PERCENTILE to 0.5 is equivalent to using MEDIAN with the same data set. *Sample: PERCENTILE(\[Effort], 0.3)*

## Date Calculations

Date functions used for aggregating data over periods of time

#### MINUTE(date expression)

Returns the date, truncating seconds of the expression.\
\&#xNAN;*Sample: MINUTE(\[Create Date])*

#### HOUR(date expression)

Returns the date truncating minutes of the expression.\
\&#xNAN;*Sample: HOUR(\[Create Date])*

#### DAY(date expression)

Returns the date of the expression (DD-MMM-YYYY).\
\&#xNAN;*Sample: DAY(\[Create Date])*

#### WEEK(date expression)

Returns the week of the date (week starts from Monday).\
\&#xNAN;*Sample: WEEK(\[Date])*

#### MONTH(date expression)

Returns the month of the date.\
\&#xNAN;*Sample: MONTH(\[Create Date])*

#### QUARTER(date expression)

Returns the quarter of the date.\
\&#xNAN;*Sample: QUARTER(\[Date])*

#### YEAR(date expression)

Returns the year of the date.\
\&#xNAN;*Sample: QUARTER(\[Create Date])*

#### AUTO(date expression)

Returns a date using the most suitable date aggregation (DAY, WEEK, MONTH and etc.) automatically based on differences between the minimum and maximum of date expressions in the data set.\
\&#xNAN;*Sample: AUTO(\[Create Date])*

#### TIMELINE(start field, end field \[, stop on current date, timeline start field, timeline end field])

Special aggregation function used for grouping data by date period, defined by start and end. Optional stop on the current date can be true or false to indicate if a timeline should be stopped at the current date. Optional timeline start and end fields are used to get min and max dates for date scale.\
\&#xNAN;*Sample: TIMELINE(\[Date], \[Valid To])*

#### TODAY() or NOW()

Returns the current date as date value.\
\&#xNAN;*Sample: TODAY()*

#### DATEDIFF(start, end, unit)

Calculates the number of days, months, etc. between two dates. Available units are year, month, week, day, hour, minute.\
\&#xNAN;*Sample: DATEDIFF(\[Date], \[Valid To], 'day')*

#### DATE(string expression)

Converts a provided date string in a known format to a date value.\
\&#xNAN;*Sample: DATE('1 Jan 2018')*

## Text Calculations

#### {text 1} + {text 2}

Concats two strings\
\&#xNAN;*Sample: \[Members] + ' is super hero'*

####

#### UPPER(text expression)

Converts text to upper case.\
\&#xNAN;*Sample: UPPER(\[Board])*

#### LOWER(text expression)

Converts text to lower case.\
\&#xNAN;*Sample: LOWER(\[Board])*

#### LEFT(text expression, N)

Returns first N symbols of text.\
\&#xNAN;*Sample: LEFT(\[Board], 3)*

#### RIGHT(text expression, N)

Returns last N symbols of text.\
\&#xNAN;*Sample: RIGHT(\[Board], 3)*

#### REPLACE(text expression, text to be replaced, replacement)

Replaces text to be replaced in an income text expression with its replacement\
\&#xNAN;*Sample: REPLACE(\[Board], 'Vizydrop', 'VZDRP')*

#### LENGTH(text expression)

Returns the length of text.\
\&#xNAN;*Sample: UPPER(\[Board])*

## **Logical operations and functions** 

#### {expression 1} == {expression 2}

Returns \`TRUE\` if the two specified values are equal, and \`FALSE\` otherwise.\
\&#xNAN;*Sample:"Oleg" == "Katrin"*

####

#### {expression 1} != {expression 2}

Returns \`TRUE\` if the two specified values are not equal, and \`FALSE\` otherwise.\
\&#xNAN;*Sample: 13 != 2\*(2 + 4)*

####

#### {expression 1} > {expression 2}

Returns \`TRUE\` if the first argument is strictly greater than the second, and \`FALSE\` otherwise.\
\&#xNAN;*Sample: 3 > 2*

####

#### {expression 1} >= {expression 2}

Returns \`TRUE\` if the first argument is greater than or equal to the second, and \`FALSE\` otherwise.\
\&#xNAN;*Sample: 3 >= 2*

####

#### {expression 1} < {expression 2}

Returns \`TRUE\` if the first argument is less than the second, and \`FALSE\` otherwise.\
\&#xNAN;*Sample: 2 < 12*

####

#### {expression 1} <= {expression 2}

Returns \`TRUE\` if the first argument is less than or equal to the second, and \`FALSE\` otherwise.\
\&#xNAN;*Sample: 2 <= 2*

####

#### {logical expression 1} OR {logical expression 2}

Returns \`TRUE\` if any of the provided arguments are logically true, and \`FALSE\` if all of the provided arguments are logically false.\
\&#xNAN;*Sample: 2 <= 1 OR 1 != 0*

####

#### {logical expression 1} AND {logical expression 2}

Returns \`TRUE\` if all of the provided arguments are logically true, and \`FALSE\` if any of the provided arguments are logically false.\
\&#xNAN;*Sample: (2 <= 1 AND 1 != 0) AND "Oleg" != "Katrin"*

####

#### IF(logical expression, expression 1, expression 2)

Returns expression 1 if a logical expression is \`TRUE\`, and expression 2 if it is \`FALSE\`.\
\&#xNAN;*Sample: IF("Oleg" != \[Members], \[Effort] + 10, \[Effort] + 5)*

####

#### IFNONE(expression, default)

Replaces NULL with the specified default value.\
\&#xNAN;*Sample: IFNONE(\[Members], 'Not Assigned')*

####

#### IS\_NULL(expression)

Returns \`TRUE\` if expression is null or undefined, and \`FALSE\` otherwise.\
\&#xNAN;*Sample: IS\_NULL(\[Board])*

####

#### IS\_NOT\_NULL(expression)

Returns \`TRUE\` if expression is not null or undefined, and \`FALSE\` otherwise.\
\&#xNAN;*Sample: IS\_NOT\_NULL(\[Board])*

####

#### CONTAINS({text expression}, {text})

Returns \`TRUE\` if expression contains the defined text, and \`FALSE\` otherwise.\
\&#xNAN;*Sample: CONTAINS(\[Members] , 'Katrin')*

####

#### LIKE({text expression} , {pattern})

Returns \`TRUE\` if expression matches the defined pattern, and \`FALSE\` otherwise.\
\&#xNAN;*Sample: LIKE(\[Members], 'Kat%')*

####

#### TEXT\_AGG({text expression})

Combines all corresponding values of categorical variables for data points into a single value. *Sample: TEXT\_AGG(\[Members])*