Table transformer - show subtraction result of 2 Jira filters

ΣΟΦΙΑ ΦΟΡΑΔΟΥΛΑ May 22, 2024

I have a table Transformer and I want in one column of this table to show number of open defects difference today morning MINUS yesterday morning at same time.

So i use the filters:

filter-1: # of open defects this morning: status was in (Open, "In Progress") on startOfDay(0) 

filter-2: # of open defects yesterday morning: status was in (Open, "In Progress") on startOfDay(-1) 

Are these correct?

And then how do i display in my table inside Table Transformer the delta #of open defects between today and yesterday: filter-2 MINUS filter-1?

2 answers

2 votes
Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
May 22, 2024

Hi @ΣΟΦΙΑ ΦΟΡΑΔΟΥΛΑ ,

Here I can give you a general example how to operate with two tables within the Table Transformer macro (there is no any difference if the tables are manually created or macro generated). You may adjust this example to your case. If you are stuck with smth, please welcome to our support portal that is confidential. There you'll be able to share your data and we'll look into the issue closely.

Wed 18-1.png

SELECT T1.'Data',
T1.'Value' AS 'Value from 1st table',
T2.'Value' AS 'Value from 2nd table',
T1.'Value' - T2.'Value' AS 'Delta value'
FROM T1 JOIN T2 ON T1.'Data' = T2.'Data'

Wed 18-2.png

Hope it gives you some hints to proceed.

 

0 votes
ΣΟΦΙΑ ΦΟΡΑΔΟΥΛΑ May 22, 2024

thank you!

 

but... i don't want to show a data-table-1 (for filter-1) and a data-table-2 (for filter-2), but have only one column in an already existing column showing directly the difference: filter-2 MINUS filter-1

Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
May 22, 2024

The lines in bold are optional:

SELECT T1.'Data',
T1.'Value' AS 'Value from 1st table',
T2.'Value' AS 'Value from 2nd table',
T1.'Value' - T2.'Value' AS 'Delta value'
FROM T1 JOIN T2 ON T1.'Data' = T2.'Data'

If you use the following query, you'll see only one column that is 'Delta value':

SELECT 
T1.'Value' - T2.'Value' AS 'Delta value'
FROM T1 JOIN T2 ON T1.'Data' = T2.'Data'

The 'Data' column serves as a key column to lookup your source tables.

Maybe it will be easier to approach your issue through our support portal that is confidential. You'll be able to attach the two screenshots of how your Jira tables look in a published state (with visible headers and 2-3 data rows). And we'll be able to provide you with a custom SQL query.

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events