table transformer to do calculations based on previous row

[emagnun]

I'm trying to do a very basic calculation to find my sprint weight% and Progress% based on my sprint plan.

Below is my table. Now, how do i calculate the data here?

Sprint Weight %= (Features Planned in the specific sprint / total features in all sprints)*100

Progress% = Current Sprint weight + Previous progress %

From a simple xls file, i can get my values as below. I have no clue how to achieve it in confluence.

[Katerina Kovriga _Stiltsoft_ · Accepted solution]

Hi @emagnun ,

I've consulted with our developers, and they provided me with a better solution for your case based on the two Table Transformer macros:

Here is the SQL query for the first Table Transformer macro that is inside:

SELECT *,
'Features Planned' / (SELECT SUM(T1.'Features Planned') FROM T1) * 100 AS 'Sprint Weight, %'
FROM T1

And here is the SQL query for the second Table Transformer macro:

SELECT *,
(SELECT SUM(TT2.'Sprint Weight, %') FROM T1 as TT2 WHERE TT2.'Sprint' <= TT1.'Sprint')
AS 'Progress, %'
FROM T1 AS TT1

Everything seems to work as it's shown on your screenshots.

[emagnun]

This is interesting. Thanks.

[Katerina Kovriga _Stiltsoft_]

Hi @emagnun ,

The Table Transformer macro doesn't allow you to refer to specific cells, it operates with table columns.

Of course, you'll be able to count the cumulative totals via the Pivot Table macro or the Table Transformer macro, but I'm afraid that such formulas where you use any previously calculated cells from the same column are not possible as for now. The Spreadsheet macro is on our roadmap and we hope to release it in 2-3 months.

Maybe I can suggest using the Table from CSV macro - you'll be able to connect your Google Spreadsheet via a URL to Confluence and proceed to filter/visualize your already aggregated data as a regular table.

[emagnun]

We would love to see the spreasheet macro soon.