In-Chart Calculations Basics

Let's dive into the basics of built-in report calculations within Visual Reports. These operations allow us to use much more powerful techniques for data manipulation and aggregation, compared to the standard Targetprocess formulas. We'll explain below why this is so.

How calculations can be added

New calculations can be added using the "+" sign, or by double clicking the 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 use any sample by clicking on it. To open the sidebar with guide & samples please try the steps below:

'Your calculations' can be also found in the sidebar. To open it just click on 'Your recent calculations' in the calculation editor.

What are the differences between Targetprocess formulas and in-chart calculations

Targetprocess formulas are created and calculated for every row in the datasource, using the wide possibilities of Targetprocess' business logic and API. On the other hand, the Visual Reports engine retrieves source rows with fields and row formulas defined by the user.

Quite often, configuring a chart will require you to apply some aggregations and post-aggregations to your data. This can be done using in-chart calculations.

Example: Let's see what happens when we need to create a cumulative scatter plot with a count of closed stories by week. Bubble size should reflect the current number of bugs with the priority level of "Fix ASAP".

Here's what you need to do to create this chart:

• First, we need to configure the datasource and its fields. So, let's choose [User Story] as the datasource and press [Create Report]. Since there is no predefined data field for a count of bugs with the priority "Fix ASAP", we need to create a formula for that:
  

• Now to configure the chart. Drop the bugs count formula field into [Size], drop [End Date] into X, and drop [Count of Records] into Y.

•  The User Story count needs to be accumulated over time. This is possible by using the RUNNING_SUM function. So, we need to replace COUNT([Id]) with RUNNING_SUM(COUNT([Id])) using in-chart calculations.
  
  

More cases which can be handled by using in-chart calculations

Below, you can find several examples of visualizations that can be created using in-chart calculations. Please let us know if you'd like us to add any cases:

• When you need to generate calculations over aggregate data with certain conditions. For example: comparing the effort of completed bugs against the completed effort of stories, and without stories over time.

  SOURCE:Bugs
  X:MONTH([End Date])
  Y:SUMIF([Effort], IS_NULL([User Story Id])), SUMIF([Effort], IS_NOT_NULL([User Story Id]))

• When need to compare the effort of completed stories over time.

  SOURCE:User Stories
  X:MONTH([End Date])
  Y:DIFFERENCE(SUM([Effort Completed]))

• When you need to see unique counts over categories or dates. For example: a user stories count of bugs created over time.

  SOURCE:Bugs
  X:MONTH([Create Date])
  Y:COUNT_DISTINCT([User Story Id])

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], [Project] == 'Targetprocess')

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], [Project] == 'Targetprocess')

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], [Project] == 'Targetprocess')

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], [Project] == '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], [Project] == 'Targetprocess')

COUNT_DISTINCT(field)

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

COUNTIF_DISTINCT(field, condition)

Returns the conditional count of distinct (different) field values within a series.
Sample: COUNTIF_DISTINCT([Feature], [Project] == '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(SUM([Time Spent])) 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_AVG calculation
(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 for second element value) 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([Time Spent])) 

RUNNING_MAX(aggregation(numerical expression))

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

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 bugs source with  MONTH([EndDate]) and COUNT([Id])

RUNNING_DIFF(Count([Id])) will give us

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 dataset.
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 dataset.
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([End Date])

HOUR(date expression)

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

DAY(date expression)

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

WEEK(date expression)

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

MONTH(date expression)

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

QUARTER(date expression)

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

YEAR(date expression)

Returns the year of the date.
Sample: YEAR([End Date])

AUTO(date expression)

Returns date using the suitable dates aggregation (DAY, WEEK, MONTH and etc.) automatically based on differences between minimum and maximum of date expression in data set.
Sample: AUTO([End 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 current date can be true or false to indicate if timeline should be stopped on current date. Optional timeline start and end fields are used to get min and max dates for date scale.
Sample: TIMELINE([Create Date], [End Date])

TODAY() or NOW()

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

DATEDIFF(start, end, unit)

Calculates the number of days, months and etc. between two dates. Available units are year, month, week, day, hour, minute.
Sample: DATEDIFF([Create Date], [End Date], 'day')

DATE(string expression)

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

Text functions 

{text 1} + {text 2}

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

UPPER(text expression)

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

LOWER(text expression)

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

LEFT(text expression, N)

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

RIGHT(text expression, N)

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

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

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

LENGTH(text expression)

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

TEXT(numeric expression)

TEXT(date expression)

Primary purpose is to convert numbers and dates into text (string) expressions. As result, numbers, dates, and texts can be merged to long text expressions. As the secondary purpose, it helps to process "number is empty" and "date is empty" cases. Empty values are converted to "No value" text expressions, and not excluded from charts.

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" != [Assigned User], [Effort] + 10, [Effort] + 5)

IFNONE(expression, default)

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

IS_NULL(expression)

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

IS_NOT_NULL(expression)

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

CONTAINS({text expression}, {text})

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

LIKE ({text expression}, {pattern})

Returns `TRUE` if expression matches the defined pattern, and `FALSE` otherwise.
Sample: LIKE([Assigned User] , '_Kat%')

TEXT_AGG ({text expression})

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