Date: Mon, 24 Jan 2022 19:30:48 +0000 (UTC) Message-ID: <860588556.41.1643052648643@3d01d9bc83fb> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_40_1366709141.1643052648643" ------=_Part_40_1366709141.1643052648643 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html Calculated Field Formulas

=20
=20

Formulas

Formulas are constructed by stringing together operators, values and fun= ctions in a way that a single value results from them. Different elements i= n the formula give different numbers of values:

• Literal values, fields, and mathematical functions all result in one value

• Relation functions can resu= lt in multiple values, depending on the number= of issues that are involved

• Operators or aggregating functions reduce multiple values into one

A formula must result in a single value, so subtasks('Quantity') is invalid, but = sum(subtasks('Quantity')) is valid.

Literal Values

You can use constant values in your formulas. Their type is determined = by their format.

Number

To use a number constant, just use the number itself, for example = 12000 or 42.86.

Text

You will need this for certain functions. Texts cannot be used to calcul= ate something. To make a text value, use single quotes =E2=80=98, for example =E2=80=98this is a text=E2=80=99 or 'cau= ses=E2=80=99.

Fields

You can use any custom field of type number, or text fields that contain= a number. JSU converts texts that only consist of a number to a number typ= e.

To use a custom field, type its name, for example Budget or= Quantity. If your field=E2=80=99s name contains spaces or spe= cial characters, you must surround it with double quotes, for example =E2=80=9CStory Points=E2=80=9D. Quotes are optional for values with= out spaces or special characters, e. g. "Quantity".

Operators

Operators combine two values. The following operators are supported:

Operator

Description

Example

+

12 + Quantity

-

Subtraction

15 - Rating

*

Multiplication

Result * Factor

/

Division

"Write off percentage" / 100

=

^

Potentiation: Raises the first value to the p= ower of the second, e. g. 28

2 ^ 8

%

Percentage: As on a calculator, you can add o= r subtract a percentage to/from a value.

Price - 10%

Parentheses

Parentheses can be used to group operations to override operator precede= nce. 2 * 3 + 3 equals 9, but 2 * (3 + 3) equals 1= 2.

Functions

Functions are versatile, we provide three types of them.

• One type consists of mathem= atical functions, to enable more calculations = you can do

• The second are aggregating = functions, where you combine multiple values i= nto one, which is necessary to reduce the results of some functions = of the third type

• Relation functions, which fetch values from other related issues, so you can get val= ues from subtasks for example

Jira automatically rounds values to three decimal places. Be careful whe= n you need exact results.

Mathematical Function

Description

Example

round(number, decimals)

Rounds the passed value to the specified numb= er of significant decimal digits.

• round(15.386, 2) =3D 15.39

• round(15.5, 0) =3D 16

floor(number)

Rounds the passed value to the nearest lower = integer.

• floor(16.34) =3D 16

ceiling(number)

Rounds the passed value to the nearest higher= integer.

• ceiling(16.34) =3D 17

abs(number)

Gives the absolute value, i. e. the positive = value.

• abs(-15) =3D 15

• abs(15) =3D 15

signum(number)

Normalises negative values to -1, zero to 0, = and positive values to 1.

• signum(-255) =3D -1

• signum(0) =3D 0

• signum(135) =3D 1

mod(dividend, divisor)

Calculates the remainder of the dividend when= divided by the divisor.

• mod(13, 2) =3D 1

Agg= regating Function

Des= cription

Exa= mple

sum(number=E2=80=A6)

Sum of the passed values

• sum(1, 4, 5) =3D 10

avg(number=E2=80=A6)

Average of the passed values

• avg(3, 5, 7) =3D 5

min(number=E2=80=A6)

Smallest of the passed values

• min(-12, 0, 346) =3D -12

max(number=E2=80=A6)

Largest of the passed values

• max(-135, 0, 1) =3D 1

Rel= ation Function

Des= cription

Exa= mple

Retrieves the values of the passed field from= all subtasks.

=20

Must be aggregated before using outside of a function.

=20

parent(field)

Retrieves the values of the passed field from= the parent of a subtask.

• parent("Quantity")

issuesInEpic(field)

Retrieves the values of the passed field from= all issues in an epic.

=20

Must be aggregated before using outside of a function.

=20
• min(issuesInEpic("Budget"))

epic(field)

Retrieves the values of the passed field from= the epic of an issue.

• epic("Price")

Retrieves the values of the passed field from= all linked issues.

=20

Must be aggregated before using outside of a function.

=20

The link type must be given in single quotes: =E2=80=98causes=E2= =80=99

More complex use cas= es

Setting a Bound= ary on Values

You need to ensure the value of the Calculated Field =E2=80=9CPrice=E2= =80=9D stays above 0, but is at most 20. A minimum bound can be set by usin= g max and a maximum using min. The price is calcu= lated using the =E2=80=9CNet Price=E2=80=9D field and the =E2=80=9CVAT=E2= =80=9D field.

1. We first need to calculate the price, which can be done using = "Net Price=E2=80=9D + VAT%

2. Then, we want to set the upper bound of 20, so we get min(=E2= =80=9DNet Price=E2=80=9D + VAT%, 20)

3. At last, we need the lower bound, so we get max(min(=E2=80=9DN= et Price=E2=80=9D) + VAT%, 20), 1)

Ensuri= ng Entered Numbers Are Integers

You have a field requesting the =E2=80=9CNumber of Participants=E2=80=9D= on a transition screen. Your subsequent calculations require this number t= o be a whole number. To ensure this, you use a Calculated Field Post Functi= on on the same transition.

1. The post function will write to the =E2=80=9CNumber of Participants= =E2=80=9D field

2. We want to operate on the same field, so we start with the formula <= code>=E2=80=9CNumber of Participants=E2=80=9D

3. Then, we want to make sure that it is a whole number. We do this by = rounding, we round up in this case: ceiling(=E2=80=9DNumber of Partic= ipants=E2=80=9D)

If the entered number is already a whole number, it is unchanged. Howeve= r, if it is not, and someone enters 15.3 for example, it is ro= unded up to the next whole number, and we get 16.
Because w= e write the result to the same field during the transition, i. e. =E2=80=9C= Number of Participants=E2=80=9D, the same field is adjusted, and we have su= ccessfully sanitised the input.

Multiple Values

The mathematical functions can also operate on the multiple values that = are returned by the relation functions. That way you can for example round = all the values returned by a subtask.

The functions accept the lists in the following ways:

• round(number_list, decimals)

• floor(number_list)

• ceiling(number_list)

• abs(number_list)

• signum(number_list)

• mod(dividend_list, divisor)

The function then is applied separately to each element in the list, and= a list with the new values is returned. The mathematical functions do not = reduce the list in size, the only way to do that is to use an aggregating f= unction.

------=_Part_40_1366709141.1643052648643--