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

[Eikra Shithil]

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:

Date

4/4/2022 

4/4/2022 

4/4/2022 

4/4/2022 

4/6/2022

4/6/2022

4/6/2022

4/10/2022

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'

FROM T*

GROUP BY 'Date'

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. 

[Natalie Paramonova _Stiltsoft_ · Accepted solution]

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:

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:

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:

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'

Option 2. Table Transformer macro

Here is the screenshot of the second macro structure:

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'
FROM T*
GROUP BY 'Date'
ORDER BY 'Date'

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'
FROM T1 AS TT1
JOIN T1 AS TT2 on TT1.'Date' >= TT2.'Date'
GROUP BY TT1.'Date'
ORDER BY TT1.'Date'

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:

Hope this helps in your case.

[Eikra Shithil]

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_]

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.

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