Calculate averages with dynamic column names

Question

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 · Answer

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.

Sat N · Answer

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

Sat N · Answer

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.