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

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

Avatar

1 badge earned

Collect

Participate in fun challenges

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

Challenges
Coins

Gift kudos to your peers

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

Recognition
Ribbon

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!

Leaderboard

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
4,463,821
Community Members
 
Community Events
176
Community Groups

How to calculate the difference between two columns of running totals

Edited

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')

2 answers

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.

Wed 2-1.png

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

Wed 2-2.png


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'


Wed 2-3.png


Hope this helps your case.

I get this error

 

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

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.

Like Stiltsoft support likes this

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

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.

Like Stiltsoft support likes this

I had to edit the question with new desired outputs. 

Suggest an answer

Log in or Sign up to answer
TAGS

Atlassian Community Events