How to calculate the difference between two columns of running totals

[Eikra Shithil]

I want to merge two tables that have a Date column and a running total column. I need to UNION dates columns from bot tables then ORDER BY Date.

Given data:

GROUP BY 'Date'

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

Desired output:

Date          | Running total  | Running Closed Total  | Difference

4/4/2022   | 4                      |                                     | 4

4/5/2022   |                         | 1                                  | 3

4/6/2022   | 7                      | 3                                  | 7

4/10/2022 | 8                      |                                     | 15

4/20/2022 |                         | 4                                  | 11

5/10/2022 |                         | 5                                  | 6

I thought it would be something like this

SUM('Running Totals' - 'Running Closed Totals')

[Katerina Rudkovskaya _Stiltsoft_]

Hi @Eikra Shithil

As I see, this question is a follow-up to this Community thread. If I’ve got your case right, you want to calculate smth similar to Created vs Resolved table based on the running totals.



Don’t forget to adjust your date format inside the Table Transformer macro:

And use the following SQL query:
CREATE TABLE TX;
INSERT INTO TX
SELECT 'Date',
SUM('Count') as 'Count',
SUM(IF('Resolved' = 'Date', 1, 0)) AS 'Count Resolved' FROM (
SELECT *, 1 AS 'Count' FROM T1
UNION All CORRESPONDING
SELECT *, T1.'Resolved' AS 'Date', 0 AS 'Count' FROM T1
WHERE T1.'Resolved' IS NOT NULL AND T1.'Date' != T1.'Resolved')
GROUP BY 'Date';
SELECT TX1.'Date',
SUM(TX2.'Count') as 'Running Total',
SUM(TX2.'Count Resolved') as 'Running Resolved',
SUM(TX2.'Count') - SUM(TX2.'Count Resolved') AS 'Difference'
FROM TX AS TX1
JOIN TX AS TX2 on TX1.'Date' >= TX2.'Date'
GROUP BY TX1.'Date'
ORDER BY TX1.'Date'

Hope this helps your case.

[Eikra Shithil]

I get this error

TypeError: Cannot use 'in' operator to search for 'length' in 1

[Katerina Rudkovskaya _Stiltsoft_]

Hi, if your tags are correct and you are on Server/Data Center, please check the version of the app that you are using.
The current one is 9.5.2, if your version is an older one please update.

[Eikra Shithil]

What other possible solutions are there to this? It does not seem to be working still.

[Katerina Rudkovskaya _Stiltsoft_]

Hi @Eikra Shithil,

Let’s move to the support portal then.

Go to the upper right corner of the page -> menu … -> View Storage Format. Copy the data and attach your page storage to the ticket. We’ll be able to recreate exactly your page with the source tables and macro settings.

If you don’t see the option, please ask your Confluence administrator to do it for you.
If your source tables are not manually created like we considered them in the Community questions but come from the Jira Issues macros, additionally attach screenshots of them in the published state without any other macros. The Jira Issues macros are not recreated through the page storage.

And please specify the version of the app/Confluence – it also helps the support team while looking into the issue.

[Eikra Shithil]

I had to edit the question with new desired outputs.