You're on your way to the next level! Join the Kudos program to earn points and save your progress.
Level 1: Seed
25 / 150 points
1 badge earned
Challenges come and go, but your rewards stay with you. Do more to earn more!
What goes around comes around! Share the love by gifting kudos to your peers.
Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!
Join now to unlock these features and more
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.
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
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')
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
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';
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.
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.