Max Date with Nested SELECT statements

Leona_y8vkcf

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}

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.