nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

Accepted answers

Katerina Kovriga {Stiltsoft}

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:

Tue 1-1.png

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

Tue 1-2.png

Tue 1-3.png

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

Tue 1-4.png

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

All comments

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.