Table Transformer - calculating target quarter

[Megan]

I am using the Table Transformer Macro from tickets coming in from Jira with a Planned End Date. I want to take that date and add a new column titled Target Quarter, based on delivery. 

Currently all columns are returning with the else FY24Q3, regardless of if they fit the constraints of the other quarters. What is incorrect here?

SELECT
*,
CASE
WHEN
'Planned End Date' >= "Jun 01, 2023"
AND 'Planned End Date' <= "Aug 31, 2023"
THEN
"FY24Q1"
WHEN
'Planned End Date' >= "Sep 01, 2023"
AND 'Planned End Date' <= "Nov 30, 2023"
THEN
"FY24Q2"
ELSE
"FY24Q3"
END
AS 'Target Quarter'
FROM
T1

[Stiltsoft support · Accepted solution]

Hi @Megan ,

Seems that your SQL query is correct but you need to check the Date format inside the Table Transformer macro (Settings tab -> Date format field):

SELECT *,
CASE
WHEN 'Created' >= "Aug 15, 2023" AND 'Created' <= "Aug 31, 2023"
THEN "15-31"
WHEN
'Created' >= "Aug 01, 2023" AND 'Created' <= "Aug 14, 2023"
THEN "01-14"
ELSE "Not August"
END
AS 'Target Quarter'
FROM T1

Hope it helps your case.