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 ?

[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