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,297,452
Community Members
 
Community Events
165
Community Groups

Max Date with Nested SELECT statements

Hi,

I have the following nested SELECT statements in Table Transformer, I'm working to get the MAX due date printed out.

I received a "TypeError: Cannot read properties of undefined (reading 'Resolution')"

SELECT *,
ROUND('Done'/'Total'*100, 2) + "% complete, expected to be " + ROUND( ('Total' - 'Greater Than Target Date') / 'Total' *100, 2) + "% complete by 6/30 and 100% by " + FORMATDATE(MAX(T1.'Due')) AS 'Summary'
FROM ( SELECT
    SUM(IF(T1.'Status' ="Blocked", 1, 0)) AS 'Blocked',
    SUM(IF(T1.'Due' >"Jun 30, 2022", 1, 0)) AS 'Greater Than Target Date',
    SUM(IF(T1.'Status' IN ("Done, Closed"), 1, 0)) AS 'Done', 
    COUNT(IF (T1.'Key' IS NOT NULL, 1, 0)) AS 'Total'
FROM T*)
WHERE T1.'Resolution' = "Unresolved"
AND T1.'Due' > "Jun 30, 2022"

Please help, what am I missing?

1 answer

1 accepted

Hi @Leona ,

As far as I can guess, the problem may be that you use the T1.'Column name' in the external SELECT.

The internal SELECT works with the source table, modifies it and it's better not referred as T1 for other manipulations.

Try to modify the query as:

FORMATDATE(MAX('Due')) AS 'Summary'

and

WHERE 'Resolution' = "Unresolved"
AND 'Due' > "Jun 30, 2022"

Hope it helps. 

If you are still stuck, please create a support ticket. Give us a screenshot of your Jira Issues macro (when the page is published so that your headers and several data rows are visible - our portal is confidential), copy the query and describe what you need to achieve. We'll guide you through the issue.

Thanks @Katerina Kovriga _Stiltsoft_ your feedback lead me to a better understanding of the nested select statements that brought me a solution.  Thanks again.

Hi @Katerina Kovriga _Stiltsoft_ 

I thought I found the solution to this only to run into an unexpected hurdle. The selected statements below works well when T2 has data. However, when T2 is empty, I get NaN% and no value (blanks) returned. 

Summary

NaN% complete, expected to be NaN% complete by 6/30 and 100% by

The SUM and FORMATDATE against T2 are where I noticed the error. I am ok with T2 returning 0, but the rest of the calculations should display. 

What would you suggest I do? Help! I am so very close to being done.

SELECT *, ROUND('Done'/'Total'*100, 2) + "% complete, expected to be " + ROUND(('Total' - 'Due ETA')/'Total' * 100, 2) + "% complete by 6/30 and 100% by " + 'Max ETA' AS 'Summary'

FROM ( SELECT *, 

'Total' - 'Done' AS 'Open',

SUM(IF(T2.'Due' IS NOT NULL, 1, 0)) AS 'Due ETA',

FORMATDATE(MAX(T2.'Due')) AS 'Max ETA'

FROM (

SELECT

    COUNT(IF (T1.'Key' IS NOT NULL, 1, 0)) AS 'Total',

    SUM(IF(T1.'Status'

        IN ("Done", "Duplicate", "Closed", "Cancelled", "Won" + CHAR(39) +"t Do", "Closed Won" + CHAR(39) +"t Do",  "Won" + CHAR(39) +"t Fix") , 1, 0)) AS 'Done',

    SUM(IF(T1.'Status' ="Blocked", 1, 0)) AS 'Blocked'

FROM T1), T2

)

Hi @Katerina Kovriga _Stiltsoft_ ,

I found a work around. If you have a cleaner solution, please advise. This is my workaround:

SELECT *,  ROUND('Done'/'Total'*100, 2) + "% complete, expected to be " + ROUND(('Total' - 'Due ETA')/'Total' * 100, 2) + "% complete by 7/31 and 100% by " + IF('Due ETA' > 0, (SELECT FORMATDATE(MAX(T1.'Due')) FROM T1 WHERE T1.'Resolution' = "Unresolved"), "Jul 31,2022") AS 'Summary',

'Total' - 'Done' AS 'Open'

FROM ( SELECT 

    SUM(IF(T1.'Status'

        IN ("Done", "Duplicate", "Closed", "Cancelled", "Won" + CHAR(39) +"t Do", "Closed Won" + CHAR(39) +"t Do",  "Won" + CHAR(39) +"t Fix") , 1, 0)) AS 'Done',

    SUM(IF(T1.'Status' ="Blocked", 1, 0)) AS 'Blocked',

    COUNT(IF (T1.'Key' IS NOT NULL, 1, 0)) AS 'Total',

    SUM(IF(T1.'Due' > "Jul 31, 2022", 1, 0)) AS 'Due ETA'

FROM T*)

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Confluence

An update on Confluence Cloud customer feedback – June 2022

Hi everyone, We’re always looking at how to improve Confluence and customer feedback plays an important role in making sure we're investing in the areas that will bring the most value to the most c...

44 views 0 1
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you