Last updated 10 months ago

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.

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.

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

â€‹

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

â€‹

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

â€‹

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

â€‹

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

â€‹

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

â€‹

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

â€‹

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

â€‹

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

â€‹

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

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

â€‹

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

â€‹

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

â€‹

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

â€‹

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*

â€‹

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

â€‹

Returns the running min for the given expression from the first row to the current.
*Sample: RUNNING_MIN(SUM([Effort])) *

â€‹

Returns the running max for the given expression from the first row to the current.
*Sample: RUNNING_MAX(SUM([Effort])) *

â€‹

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 |

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

â€‹

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

â€‹

Returns the absolute value of a number
*Sample: ABS(-3) will return 3*

â€‹

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

â€‹

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 functions used for aggregating data over periods of time

â€‹

Returns the date, truncating seconds of the expression.
*Sample: MINUTE([Create Date])*

â€‹

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

â€‹

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

â€‹

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

â€‹

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

â€‹

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

â€‹

Returns the year of the date.
*Sample: QUARTER([Create Date])*

â€‹

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

â€‹

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

â€‹

Returns the current date as date value.
*Sample: TODAY()*

â€‹

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

â€‹

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

â€‹

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

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

â€‹

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

â€‹

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

â€‹

Returns last N symbols of text.
*Sample: RIGHT([Board], 3)*

â€‹

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

â€‹

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

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

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

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

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

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

Returns `TRUE` if the first argument is less than or equal to the second, and `FALSE` otherwise.
*Sample: 2 <= 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*

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"*

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

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

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

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

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

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

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