Unable to count/sum column values

[Ramu]

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)>.

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

Please help .. 

[Stiltsoft support · Accepted solution]

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.

[Stiltsoft support]

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

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'

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.

[Ramu]

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 ..

[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.

[Ramu]

Hey ..

on the same thread from your suggestion of Table Spreadsheet, it works but not helping coz, there are concerns as follows:

1. Unable to expand the Table view size (now there is an option to expand it to the screen full max which is not helping .. there is nothing like drag to an extent or to some pixels size etc ..)

2. I want this page to be visited by resources and add their own numbers every week .. currently, they can but once everyone add the numbers, there is no option to Save ..

Tried checking for it the 'File' menu , pressing ctrl+s etc .. but there is no option .. which beats the whole purpose ..

(there is one auto save  but again, not helping coz .. i want a Save option every time when someone visit the page and update their numbers and they have to Save)

My Table spreadsheet in Confluence:

[Stiltsoft support]

Hi @Ramu , 

You may read about the settings of the macro height here in our documentation: https://docs.stiltsoft.com/tfac/cloud/how-to-use-table-spreadsheet-macro-83100938.html#HowtouseTableSpreadsheetmacro-SpreadsheetMacroheight

What concerns the option to save changes, it may be a manual save (you add some changes -> see the red star near the File menu -> go to the File menu and click the Save option) or an Autosave.

You may read about the Autosave here: https://docs.stiltsoft.com/tfac/cloud/how-to-use-table-spreadsheet-macro-83100938.html#HowtouseTableSpreadsheetmacro-SpreadsheetAutosave

[Ramu]

Hey ..

what ever you have mentioned .. yes i have tried  BUT, the only main problem/concern is that whatever changes i do it is not getting 'Auto saved' INSPITE of enabling the Auto Save option in the settings. So, a refresh on the page after AUTOSAVE enabled is again loading the initial page and NOT the updated one.

Also, i dont have a SAVE option in my File menu:

[Stiltsoft support]

As usual, it happens when you don't have the permissions to edit the page.

Are you sure that you've inserted the macro yourself and not trying to update it from the page view mode on some other page where you are not an editor?

The Save and Save as options should be always present in the File menu if you have all the permissions.

[Ramu]

im sure im the one inserted the Macro and it is on the same page .. i have full admin rights on the page i tried coz it is my personal Confluence page ..

not sure why the Save option is not there !!

[Stiltsoft support]

Then refer to our support: attach a page storage (upper right corner -> menu ... -> View storage format), insert a new Table Spreadsheet macro and update it while recording a har-file (also attach it to the ticket) and define what hosting you use (Cloud/Data Center and specify the version of the app if you are on Data Center).

[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]

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 :)