Calculate averages with dynamic column names

[Sat N]

Hi,

We do a evaluation every other month for 3 processes which have different criterias.
Every other month I create a table excerpt like the one shown below from a table spreadsheet, so for Oct, it would be like

For Aug, also similar table excerpt exists (and there will be an excerpt for Dec and so on..)

So I used a table transformer to join both the excerpts on "Process Details" to get the following view.  (I need to use an OUTER JOIN as the criterias might change over time )

Now I have a Summary excerpt, this is maintained manually ( because I enter comments which changes every other month) which would have 3 rows ,one for each process. But here the column name changes over time, so you see Oct and Aug now (but soon it will be Dec and Oct).

My goal : To calculate average per process and month combination into the Summary, so it will eventually look like below using the data in the table transformer

 I was able to calculate averages using the month name as column names using queries like "SET @octnov_p2_avg_value = (SELECT AVG(CAST(T2.'Oct/Nov' AS DECIMAL)) FROM T2 WHERE ABS(T2.'Oct/Nov') > 0 AND 'Process' = "P2") AND then use UPDATE statement. (The columns will contain non-numeric data also which I filtered out.)

But I would ideally like to calculate averages by knowing the Month names from the Summary and using them to fetch data from the combined view in the transformer,so the month names are not hardcoded anywhere. Can this be done ?

[Stiltsoft support]

Hi @Sat N ,

Judging by the macro names, you use our Table Filter, Charts & Spreadsheets for Confluence app.

Seems that the behavior is an expected one: the Table Transformer macro treats its source table as a mini database, so you call out column names to perform calculations and transformations.

The workaround may be to check both the "Transpose source table" and "Transpose result table" options and using the following query:

SELECT ROWNUM() AS '0', * FROM T*

Then your columns after the JOIN will be named as numbers and your next month will be the column '2', the previous one - '5'.

But in general it seems that the case involves a lot of manual efforts and extra macros. At the beginning of the question you've mentioned that you use the Table Spreadsheet macro - why don't you, for example, create a separate tab for each month and another summary tab for comparison? You can easily refer to the cells from other sheets and copy/paste formulas.

Then if you need to present your results somewhere, you may use the Table Spreadsheet Include macro and choose from what spreadsheet, what sheet and what cell range you need to 'grab'. The reused content will look as a native non-editable Confluence table. 

[Sat N]

Hi @Stiltsoft support 
Thanks for your help. I have not tried out your solution to use the transpose option twice and the subsequent query yet.

But let me answer your question about the usage of tabs.  The image below is my understanding of your proposal to create seperate tabs. And I have 'n' such teams. The reason I don't prefer it is because it involves adjusting formulas every time a month is added  for 'n' teams. (In fact, this is a running activity for me where already excels are in use. I am trying to find a better solution than excels). (How nice if I could just provide the month names and the criteria scores and no more adjusting formulas !)


I want to have a maintainable low code solution- in fact, in the image above, row 5 to row 17 (lets call it "Criteria Summary") was created by the table transformer as shown in my first post - and it was scalable by just adding the month excerpts to the transformer- no need to create/ edit formulas.

But I could not create the "Process Summary" (Row 2 & 3) without hardcoding the month names. I am looking for a scalable solution - and I will create a month "Dec" in Process Summary , since anyway, I need to update comments. But from then on, the tool must look into the "Process Summary" to identify the months involved,  and then refer to the "Criteria Summary" (criteria data for "Dec" will then be available), calculate averages for the Process-Month combo and  accordingly UPDATE the relevant cells in "Process Summary". And no more formulas to copy/paste.

[Stiltsoft support]

Yes, the idea was smth like this: take the one whole year for the first team and create 12 tabs (one tab per month). Then you create the 13th tab and using cells' reference from other tabs create formulas to calculate the required difference (progress or how do we call it).

Then you may download this file as a .tfss and use it as a template (all the formulas and formatting will stay). This file may be saved somewhere on your PC.

Then when you need to do the same for the second team, third team and so on, you insert a new Table Spreadsheet macro and choose to import this .tfss template.

Then you'll need to correct only your inputs for each month and the 13th summary tab will be calculated automatically.

Seems that this is all that we can suggest as for now.

By the way if you have further questions regarding the app, you may contact our support portal directly. Here we try to find relevant posts manually but maybe we miss smth. But on the portal each question is registered and the portal itself is confidential so we'll be able to exchange screenshots and page storages freely.

[Humashankar VJ]

Hi @Sat N 

Calculating averages dynamically in Confluence without hardcoding month names is achievable by leveraging advanced techniques with Table Transformer and SQL-like queries.

• The objective is to dynamically reference column names, such as Oct, Aug, or Dec, from the Summary excerpt and utilize them to calculate averages in a combined view.
• Given that you're working with dynamic columns and aiming to avoid hardcoding month names, which are subject to change, you can employ JQL or Table Transformer macros to dynamically adjust your queries based on the current month or summary data.
• To accomplish this, you'll need to establish a dynamic column reference by extracting month names dynamically from the Summary excerpt.
• This can be achieved by having a row with month names in the Summary excerpt, which can then be referenced using Table Transformer or similar plugins.

By assigning the month names to variables, you can ensure that you're referencing the correct month column dynamically, rather than relying on hardcoded month names. For instance, you can use a query like "SET @month_col = (SELECT ColumnName FROM Summary WHERE RowName = 'MonthNames');" to dynamically reference the month column.

Hope this helps - Happy to help further!!
Thank you very much and have a great one!
Warm regards

[Sat N]

Hi @Humashankar VJ 
Thanks for your time and support. I dont know JQL and I tried to google your comment about "RowName" - did not understand what is rowname. Is it a keyword ?

Secondly assume that I could get the month name dynamically. The code below in simple English is my task

1. Loop through all the column headers in the summary table. Store the month name(s) in some variable.
2. Then refer to the "Criteria summary" - the output given by the transformer in my first post. For each month identified in step1, group by the process. Then calculate the average for the Process-Month combo. 
This need to be say, in a while loop, till we calculate for all the months identified in step1.
3. Then UPDATE the Summary , in the right cells for the process month combo

This way there is no hardcoding of the month names while calculating average.

I hope you can guide me here.
Thanks