Advanced Formula Widget

Overview

The Advanced Formula widget displays the results of multiple mathematical calculations based on numerical fields or work item count from multiple shared queries.

Compared to the standard Formula widget, it supports up to 9 calculation results instead of a single value, allows combining data from up to 9 shared queries, and enables more advanced calculations such as percentages, ratios, or custom formulas in addition to basic operations like SUM, COUNT, AVG, MIN, and MAX.

This widget is ideal for displaying key numbers, metrics, and KPIs, such as the number of urgent items, the amount of remaining work, percentage of work completion, or bug reopening ratio.

How does it work?

The widget lets you define up to 9 variables ('a', through 'i') each calculated as COUNT, SUM, MAX, MIN or AVERAGE for a numeric field from a specified query. You can then use these variables to create up to nine mathematical formulas and display the results in your dashboard, giving you a powerful tool to track and visualize multiple key metrics simultaneously.

Configuration

Setting	Description
Widget title	Choose what to display in the title bar of the widget.
Variables	Select the variable(s) 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h' or 'i' that you need and specify how they are calculated. For each of them, choose the Formula (SUM, MIN, MAX, COUNT or AVERAGE) that is applied against the selected Field of the worked items returned by the selected shared query.
Data to display	Choose which values appear in the widget by writing Math formulas that use one or more of the selected variables. For example: a — displays the value of calculated variable a a + b + c — displays the sum of calculated variables a, b, and c (b / a) * 100 — displays a percentage or ratio For each math formula, enter a Label and specify with how many Decimals the formula result shall be displayed. Optionally, for each displayed value, you can define a Prefix and/or Suffix of up to 10 characters to indicate the unit of measure. A prefix (for example, $) appears before the calculated value (e.g., $8500.50), while a suffix (for example, %) appears after the value (e.g., 55.69%). If you enable Format with commas, values such as $8500.50 will be displayed as $8,500.50. Color Settings You can customize the color of the displayed value by clicking the green square on the right. By default, values appear in green, but you can select any color to better match your preferences or design needs. Choose how the displayed results should be colored: Fixed color - choose this option if you want to apply a fixed chosen color, regardless of the result value. Number colored by value - choose this option if you want to apply different colors to specified value ranges. In this case, you need to set custom limits and colors. If you check Extra highlight by coloring border, the widget will color the border in the same color.

Example

Suppose you want to display the percentage of work completed (measured in story points) for a project. Follow these steps:

1. Create two shared queries:

   • One query that returns all items in the project.

   • Another query that returns only the completed items in the project.

2. Define two variables:

   • a — calculated as the SUM of the Story Points field from the first query. This represents the total estimated work (in story points).

   • b — calculated as the SUM of the Story Points field from the second query. This represents the total completed work (in story points).

3. Display the values using math formulas:

   • a — displays the total estimated work.

   • b — displays the total completed work.

   • (b / a) * 100 — calculates and displays the percentage of work completed.

You can follow the same approach to calculate and display other key project metrics.