Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root


1 badge earned


Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!


Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.


Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!


Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
Community Members
Community Events
Community Groups

How to count number of rows for date then create cumulative count?


I would like to count first the number of row entries for every date then get the running total. This is for custom transformation in the Table Transformer Macro.

Example Data:












Desired Output:


Date          | Count | Running total

4/4/2022   | 4         | 4

4/6/2022   | 3         | 7

4/10/2022 | 1         | 8



I have tried: 

SELECT 'Date',

COUNT(*) AS 'Count',

SUM('Count') OVER (ORDER BY 'Date') AS 'Running total'



ORDER BY 'Date';


and various other variations of this code but it does not work. I can successfully get the count but not running total. 

1 answer

1 accepted

1 vote
Answer accepted
Natalie Paramonova [Stiltsoft]
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
Nov 24, 2022

Hi @Eikra Shithil ,

We can suggest two available options for the case for you to choose from.

Option 1. Pivot Table + Table Transformer

Here is the structure for you to implement:

Tue 2-1.png

If your tags are correct and you are on Confluence Cloud, please use our Table Toolbox to nest macros.

Here the original table is reused via the Table Excerpt/Table Excerpt Include macros, so you will have the same data for the upper and lower Pivot Table macros.

Here are the settings for the upper Pivot Table macro:

Tue 2-2.png

Tue 2-3.png

Also check the “Hide totals” option, we don’t need it for the case.

And here are the settings for the lower Pivot Table macro:

Tue 2-4.png

The Data Source tab looks the same, but for the Options tab we additionally check the “Cumulative count” option.

Now we move to the Table Transformer macro and join our tables with the help of the following SQL query:

SELECT T1.'Date', T1.'Count',
T2.'Count' AS 'Running totals' FROM T1
OUTER JOIN T* ON T1.'Date' = T*.'Date'

Tue 2-5.png

Option 2. Table Transformer macro

Here is the screenshot of the second macro structure:

Tue 3-1.png

Here we transform the original table and then reuse the transformed table once more for further transformation – please be careful to place your Table Excerpt/Table Excerpt Include macros in the right places and don’t forget about the Table Toolbox macro if you are on Cloud.

Here is the SQL query for the upper internal Table Transformer macro:

SELECT 'Date',
COUNT('Date') AS 'Count'

Tue 3-2.png

Here we count rows as you may see. And don’t forget to set an appropriate Date format inside the Table Transformer macro – “m/d/yy” for your example.

Now we move to the lower internal Table Transformer macro and calculate the running totals:

SELECT 'Date',
SUM (TT2.'Count') AS 'Running totals'
JOIN T1 AS TT2 on TT1.'Date' >= TT2.'Date'

Tue 3-3.png

And don’t forget about the Date format again.

Now we go to the external Table Transformer macro and look up our transformed tables by the “Date” column:

Tue 3-4.png

Tue 3-5.png

Hope this helps in your case.

Thank you for the answer, I went with option 2 because my data is very large and having a lot of macros really slows down the load time for the page. I initially wanted to try and do it all within one Table Transformer but if this is the best option I will stick with it. I have a follow-on question. 

Given data:

Date          |  Status   |  Date Closed

4/4/2022   |  Open    |  

4/4/2022   |  Closed  |  4/5/2022

4/4/2022   |  Closed  |  5/10/2022

4/4/2022   |  Closed  |  4/6/2022

4/6/2022   |  Open    |  

4/6/2022   |  Closed  |  4/6/2022

4/6/2022   |  Open    |  

4/10/2022 |  Closed  |  4/20/2022


Desired Output:

Date          | Running total  | Running Closed Total

4/4/2022   | 4                      | 

4/5/2022   |                         | 1

4/6/2022   | 7                      | 3

4/10/2022 | 8                      | 

4/20/2022 |                         | 4

5/10/2022 |                         | 5


I tried following the above to get the running totals for all and closed and was successfully. Then I put it all into a table transformer but am having trouble merging. I can use union to combine dates but event using ORDER BY, the dates are out of order. Please assist me in figuring this out. 


Thank you

Natalie Paramonova [Stiltsoft]
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
Nov 29, 2022 • edited

For the second option we start to order dates in the upper internal Table Transformer macro. Please check once more if you copied the whole SQL query with the ORDER BY 'Date' ending and then go to the Table Transformer macro settings and check the Date format field. It should correspond with the date format in your table.

Tue 4-1.png

The same date format should be also set for other Table Transformer macros as well.

Like Stiltsoft support likes this

Suggest an answer

Log in or Sign up to answer
AUG Leaders

Atlassian Community Events