Max Date with Nested SELECT statements

[Leona]

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?

[Katerina Kovriga {Stiltsoft} · Accepted solution]

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.

[Leona]

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

[Leona]

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

)

[Leona]

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*)