Table transformer - show subtraction result of 2 Jira filters

Question

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?

Stiltsoft support · Answer

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.

ΣΟΦΙΑ ΦΟΡΑΔΟΥΛΑ · Answer

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 · Answer

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.

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'

Hope it gives you some hints to proceed.