nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Unable to count/sum column values

Ramu_kd54ib

Hey ..

I have a Confluence Table(it's a plain simple Table updated manually and no macros involved yet) where there are numeric values in say 4 to 5 columns ..

My Ask/Request is:

1. I need to add the SUM of all these values in a Column named Total ..

2. I need to subtract the Leave/Training column values from this Total column and display in the column named Availability

<i.e., Col Availability = Col Total - (Col Leave & Col Training)>.

Table count.jpg

So far, not lucky with Table filter, Transformer macros in achieving this ..

Please help ..

Accepted answers

Stiltsoft support

Hi @Ramu ,

The Table Filter and Table Transformer macro treat tables more like mini databases (refer to the column names, calculate totals per row, subtract columns and so on).

For your case the most suitable macro is Table Spreadsheet (if your table is created manually) or Spreadsheet from Table (if your table is macro generated and comes, for example, from the Page Properties Report, Table Excerpt Include, Jira Issues macros).

There you'll be able to merge cells and use Excel-like cell formulas to calculate totals and subtract them. The report can be also easily formatted as you prefer.

All comments

Stiltsoft support

Hi @Ramu ,

The Table Filter and Table Transformer macro treat tables more like mini databases (refer to the column names, calculate totals per row, subtract columns and so on).

There you'll be able to merge cells and use Excel-like cell formulas to calculate totals and subtract them. The report can be also easily formatted as you prefer.

Stiltsoft support

I've also consulted with the developers regarding the most elegant Table Transformer SQL query for the case:

Tue 2-1.png

SELECT T1.'Resource',
FORMATWIKI(SUM(T1.'Role Type' + "\n")) AS 'Role Type',
FORMATWIKI(SUM(COALESCE(T1.'Week 1', "_") + "\n")) AS 'Week 1',
FORMATWIKI(SUM(COALESCE(T1.'Week 2', "_") + "\n")) AS 'Week 2',
FORMATWIKI(SUM(COALESCE(T1.'Week 3', "_") + "\n")) AS 'Week 3',
SUM(
IF(T1.'Role Type' = "Lead", 1, 0) *
(COALESCE(T1.'Week 1', 0) + COALESCE(T1.'Week 2', 0) + COALESCE(T1.'Week 3', 0)))
AS 'Total',
SUM(
IF(T1.'Role Type' = "Lead", 1, -1) *
(COALESCE(T1.'Week 1', 0) + COALESCE(T1.'Week 2', 0) + COALESCE(T1.'Week 3', 0)))
AS 'Availability'
FROM T1 GROUP BY T1.'Resource'

Tue 2-2.png

As you can see, the SQL query is a working one but maybe it isn't so obvious as using an Excel-like spreadsheet will be.

Hope that the provided options will help your case.

Hassen Chtioui_Creativas_

Hello Vijay,

This is Hassen from the support team at Creativas, the Smart Tables marketplace vendor.

The powerful functions provided by Smart Tables can help you perform these calculations with Confluence tables. You just need to add the Smart Tables macro and insert a Confluence table.

The following screen recording illustrates how to add the functions on Cloud.

Smart Tables on Data Center supports these functions as well.

If you have any questions, please feel free to reach out to the support team. You can also book a demo for an overview of Smart Tables from here.

Ramu_kd54ib

Hey ..

Firstly, glad and a high appreciation for responding in detail .. .. it shows the seriousness you maintain on the queries asked .. High respect

now, this is not happening for me coz i have the Table in different set up and with my trail and error attempt of implementing the solution is becoming complex .. for example, im not able to enter the values or edit the values etc .... Definitely i agree it is me who is missing something here ..

So, i prefer/request you to please schedule a call support on this ... which i believe is not a too much of ask please ..

Ramu_kd54ib

THank you so much Hassen .. unfortunately, i have restrictions where i cant afford any add on on my own ...

may be in Future, definitely will check on this if possible in my Organisation

CHeers & Thanks

Stiltsoft support

If you need to share any sensitive info, you may refer to our support portal that is confidential.

And again the best option is to take the Table Spreadsheet macro where you'll be able to structure your data and use cell formulas as you wish.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Unanswered
Groups
Help

Find more posts tagged with

confluence

filter

table

table-filter

table-filter-and-charts

table-spreadsheet

table-filter-charts-spreadsheets

unknown-hosting-type

confluence-unknown-hosting-type