Calculation in tables

[Robert Czapor]

Hi,

It is possible to add some calculation inside the table? I need that column 1 will be a sum of values from columns 2, 3, 4.
What about other calculation like multiplication and division?

Robert

[Andrey Khaneev _StiltSoft_]

Also Table Filter macro can be used to outputting total columns in the table.

[CelesteCS]

Hello Robert!

For any calculations in Confluence, including calculations over tables, you may use CelesteCS Math for Confluence. There are versions for both Confluence Server and Confluence Cloud.

Assume you have a table with three rows and four columns: A till D with two rows and want to summarize elements from B2 to D2. Then here is the formula you should specify in A2:

SUM(Table1.B2:D2)

Table1 is your table, assuming it is the first table on the page.

If you want to calculate average value, use AVG function:

AVG(Table1.B2:D2)

As you see, it is very simple and is similar to EXCEL with equal set of functions.

There is a plenty of other functions in the macro, so you may perform any calculations using formulas in EXCEL style.

Thanks!

[Manupriya S]

Hi @CelesteCS  can we perform calculations between two Jira issues count macros using your plugin ?

suppose:

I have total open issues count = 50 issues {got using the Jira Macro}

Another value that i have is, total closed = 40 issues {got using the Jira Macro}

Now i would like to perform sum on the above two Jira issues macros such that my result is:

TOTAL ISSUES = 50(Total open) +40(total closed)   

Total issues= 90

So i need to build-in something dynamic - a table which fetches the values from the Jira (using a query to look for particular issues result) and perform calculations on it.

Much appreciated if you could help.

[CelesteCS]

Hello,

We've constantly got questions regarding JIRA Issues macro usage, so let me explain how Math macro works with the numbers of JIRA issues.

JIRA Issue macro returns the numbers of issues, but it works asynchronously thus cannot be used in Numerical Expression macro, which reads macro output synchronously. Thus, we have implemented a JIRAISSUECOUNT function, which returns the number of issues, which conform to the specified JQL request.

Here is an example of using this formula (I tried to make it similar to your case):

JIRAISSUECOUNT(“Project = Test1 AND Priority = Critical”)

Note 1: In case your Confluence is integrated with several JIRA instances, you may specify the JIRA instance name as the second parameter of this function.

Note 2: You may specify whole request in double quotes and specify string values inside the request in single quotes.

Here is more complicated example, using these notes:

JIRAISSUECOUNT("project = TEST AND description ~ '@mention'"; "My test JIRA")

Here is how the result looks like comparing to JIRA Issue macro:

So, that’s enough for the common theory. Now let’s go to your example.

Total open issues example:

JIRAISSUECOUNT("project = TEST AND Status = ‘Open’")

Total closed:

JIRAISSUECOUNT("project = TEST AND Status = ‘Closed’")

Sum of these two:

JIRAISSUECOUNT("project = TEST AND Status = ‘Open’") + JIRAISSUECOUNT("project = TEST AND Status = ‘Closed’")

Here is how I did it:

And here is the result:

Note 1: In my example project name is OT and statuses are Open and Done, not Closed.

Note 2: Red star signs appear in trial versions of Numerical Expression macro only. In paid versions it disappear.

And finally: if you want to make this dynamical appearance much better, you may apply conditional formatting, provided by our other product, CelesteCS Conditions for Confluence, which allows you, for example, automatically make bold red text or light green background, based on specified conditions:

Hopefully that helps!

Thanks!

[Jozef Kotlár]

Have a look at the Table Sum Plugin.

[Robert Czapor]

There is no other possibility than plugin? What about other calculaction?

[Manupriya S]

use Eval Data macro. no need of any plugins. :)