How to calculate the balance with SQL via using table transformer in JIRA?

Oujy_lala

Hi Team,

Trying to calculate the balance ticket of each month, the value should be accumulate, but seems the query ( in bold ) not works, can someone please help to check?  Thank you.

 

An example with screenshot attached:

in Oct 2024, New ticket is 24, resolve 0, Balance is 24

in Nov 2024, New ticket is 13, resolve 8, Balance is 5, but the exactly balance value i want should be balance of Oct 2024 + balance of Nov 2024 : 24+5=29

in Dec 2024, New ticket is 9, resolve 3, Balance is 6, the balance value i want is :

balance of Oct 2024 + balance of Nov 2024+ balance of Dec2024 = 24+5+6=35

......

and so forth.

 

I had use : sum(COALESCE(T1.'Count',0)) over(order by T1.'Created')
-sum(COALESCE(T2.'Count',0)) over(ORDER BY T1.'Created') AS 'Balance' 

but seems not work. 

 

-------------Here goes the SQL i had used ----------------------------------

 

 

SELECT
T1.'Created' AS 'Date',
COALESCE(sum(T1.'Count'),0) AS 'NEW Ticket',
COALESCE(sum(T2.'Count'),0) AS 'Resolved',
sum(COALESCE(T1.'Count',0)) over(order by T1.'Created')
-sum(COALESCE(T2.'Count',0)) over(ORDER BY T1.'Created') AS 'Balance' 
/*sum(T1.'Count') OVER(order by T1.'Created') AS 'Balance'*/

from T1 left OUTER JOIN T2 ON T1.'Created'=T2.'Resolved'

group by T1.'Created'

 

Nikita Kamai

Hello,

Concerning our Table Filter, Charts & Spreadsheets for Confluence app, please, try the following SQL as an alternative:

CREATE TABLE TT1;
INSERT INTO TT1
SELECT
T1.'Created' AS 'Date',
COALESCE(sum(T1.'Count'),0) AS 'NEW Ticket',
COALESCE(sum(T2.'Count'),0) AS 'Resolved ',
sum(COALESCE(T1.'Count',0)) over(order by T1.'Created')
-sum(COALESCE(T2.'Count',0)) over(ORDER BY T1.'Created') AS 'Balance' 
/*sum(T1.'Count') OVER(order by T1.'Created') AS 'Balance'*/

from T1 left OUTER JOIN T2 ON T1.'Created'=T2.'Resolved'

group by T1.'Created';

SELECT TT1.'Date',TT1.'NEW Ticket', TT1.'Resolved ', TT1.'Balance' + (SELECT COALESCE(SUM(TT2.'Balance'), 0)
FROM TT1 AS TT2 WHERE TT2.'Date' < TT1.'Date') AS 'Balance' FROM TT1

NB. Also, please, make sure the date format in the macro settings is set to M yy, as your source output suggests to make the transformation work properly.

Best wishes,
Nikita