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.

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

Numerical Calculations

+, -, *, /

Basic math functions. Sample: 2*3 - 1 + 5/2

SUM(numerical expression)

Returns the sum of a series of numbers. Sample: SUM([Effort])

SUMIF(numerical expression, condition)

Returns the conditional sum of a series of numbers. Sample: SUMIF([Effort], [Board] == 'Me')

AVG(numerical expression)

Returns the average of a series of numbers. Sample: AVG([Effort])

AVGIF(numerical expression, condition)

Returns the conditional average of a series of numbers. Sample: AVGIF([Effort], [Board] == 'Super')

MIN(numerical expression)

Returns the minimum of a series of numbers. Sample: MIN([Effort])

MINIF(numerical expression, condition)

Returns the conditional minimum of a series of numbers. Sample: MINIF([Effort], [Board] == 'Me')

MAX(numerical expression)

Returns the maximum of a series of numbers. Sample: MAX([Effort])

MAXIF(numerical expression, condition)

Returns the conditional maximum of a series of numbers. Sample: MAXIF([Effort], [Board] == 'Targetprocess')

COUNT(field)

Returns the count of rows within a series. Sample: COUNT([Id])

COUNTIF(field, condition)

Returns the conditional count of rows within a series. Sample: COUNTIF([Id], [Board] == 'Targetprocess')

COUNT_DISTINCT(field)

Returns the count of distinct (different) field values within a series. Sample: COUNT_DISTINCT([Name])

COUNTIF_DISTINCT(field, condition)

Returns the conditional count of distinct (different) field values within a series. Sample: COUNTIF_DISTINCT([Name], [Board] == 'Targetprocess')

TOTAL(aggregation(numerical expression))

Returns the total for a given expression across the whole dataset. Sample: TOTAL(SUM([Effort]))

RUNNING_SUM(aggregation(numerical expression))

Returns the running sum for a given expression from the first row to the current. 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. Sample: RUNNING_AVG(COUNT([Id])) RUNNING_AVGcalculation (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. 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. Sample: RUNNING_MAX(SUM([Effort]))

DIFFERENCE(aggregation(numerical expression))

Returns the running difference for the given expression between current row and previous. 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. Sample: BINS([Effort])

ROUND(numerical expression, places)

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

ABS(numerical expression)

Returns the absolute value of a number 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. Sample: MINUTE([Create Date])

HOUR(date expression)

Returns the date truncating minutes of the expression. Sample: HOUR([Create Date])

DAY(date expression)

Returns the date of the expression (DD-MMM-YYYY). Sample: DAY([Create Date])

WEEK(date expression)

Returns the week of the date (week starts from Monday). Sample: WEEK([Date])

MONTH(date expression)

Returns the month of the date. Sample: MONTH([Create Date])

QUARTER(date expression)

Returns the quarter of the date. Sample: QUARTER([Date])

YEAR(date expression)

Returns the year of the date. 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. 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. Sample: TIMELINE([Date], [Valid To])

TODAY() or NOW()

Returns the current date as date value. 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. Sample: DATEDIFF([Date], [Valid To], 'day')

DATE(string expression)

Converts a provided date string in a known format to a date value. Sample: DATE('1 Jan 2018')

Text Calculations

{text 1} + {text 2}

Concats two strings Sample: [Members] + ' is super hero'

UPPER(text expression)

Converts text to upper case. Sample: UPPER([Board])

LOWER(text expression)

Converts text to lower case. Sample: LOWER([Board])

LEFT(text expression, N)

Returns first N symbols of text. Sample: LEFT([Board], 3)

RIGHT(text expression, N)

Returns last N symbols of text. 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 Sample: REPLACE([Board], 'Vizydrop', 'VZDRP')

LENGTH(text expression)

Returns the length of text. Sample: UPPER([Board])

Logical operations and functions

{expression 1} == {expression 2}

Returns `TRUE` if the two specified values are equal, and `FALSE` otherwise. Sample:"Oleg" == "Katrin"

{expression 1} != {expression 2}

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

{expression 1} > {expression 2}

Returns `TRUE` if the first argument is strictly greater than the second, and `FALSE` otherwise. Sample: 3 > 2

{expression 1} >= {expression 2}

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

{expression 1} < {expression 2}

Returns `TRUE` if the first argument is less than the second, and `FALSE` otherwise. Sample: 2 < 12

{expression 1} <= {expression 2}

Returns `TRUE` if the first argument is less than or equal to the second, and `FALSE` otherwise. 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. 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. 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`. Sample: IF("Oleg" != [Members], [Effort] + 10, [Effort] + 5)

IFNONE(expression, default)

Replaces NULL with the specified default value. Sample: IFNONE([Members], 'Not Assigned')

IS_NULL(expression)

Returns `TRUE` if expression is null or undefined, and `FALSE` otherwise. Sample: IS_NULL([Board])

IS_NOT_NULL(expression)

Returns `TRUE` if expression is not null or undefined, and `FALSE` otherwise. Sample: IS_NOT_NULL([Board])

CONTAINS({text expression}, {text})

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

LIKE({text expression} , {pattern})

Returns `TRUE` if expression matches the defined pattern, and `FALSE` otherwise. 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])