failed to convert value from string to boolean

RajNadar

Can someone suggest why ETL Formul doesn't working ??

I try to get dynamic current quarter data filter from dataset

Capture6.JPG

ST_Superman

CASE

WHEN MONTH(CURDATE()) IN ('1','2','3') THEN CONCAT(RIGHT(YEAR(CURDATE()),2),'-Q1')

WHEN MONTH(CURDATE()) IN ('4','5','6') THEN CONCAT(RIGHT(YEAR(CURDATE()),2),'-Q2')

WHEN MONTH(CURDATE()) IN ('7','8','9') THEN CONCAT(RIGHT(YEAR(CURDATE()),2),'-Q3')

WHEN MONTH(CURDATE()) IN ('10','11','12') THEN CONCAT(RIGHT(YEAR(CURDATE()),2),'-Q4')

END

Try this. it worked for me

**edit

Actually, I was able to get your original formula to work as well. I'm not sure what is causing the error.

rco

Raj, are you in the Filter tile's formula editor there? Filter expressions must be boolean (true/false/null). You may want to use the Add Formula tile to create your quater string, and then perhaps multiple filter tiles with expressions like my_year_quarter = '23-Q2' to separate your data into different sets (if that's what you're trying to do).

Also, it looks like you could benefit from the QUARTER function in that formula:

CONCAT(RIGHT(YEAR(CURDATE()),2),'-Q',QUARTER(CURDATE()))

ST_Superman

CASE

WHEN MONTH(CURDATE()) IN ('1','2','3') THEN CONCAT(RIGHT(YEAR(CURDATE()),2),'-Q1')

WHEN MONTH(CURDATE()) IN ('4','5','6') THEN CONCAT(RIGHT(YEAR(CURDATE()),2),'-Q2')

WHEN MONTH(CURDATE()) IN ('7','8','9') THEN CONCAT(RIGHT(YEAR(CURDATE()),2),'-Q3')

WHEN MONTH(CURDATE()) IN ('10','11','12') THEN CONCAT(RIGHT(YEAR(CURDATE()),2),'-Q4')

END

Try this. it worked for me

**edit

Actually, I was able to get your original formula to work as well. I'm not sure what is causing the error.

rco

Raj, are you in the Filter tile's formula editor there? Filter expressions must be boolean (true/false/null). You may want to use the Add Formula tile to create your quater string, and then perhaps multiple filter tiles with expressions like my_year_quarter = '23-Q2' to separate your data into different sets (if that's what you're trying to do).

Also, it looks like you could benefit from the QUARTER function in that formula:

CONCAT(RIGHT(YEAR(CURDATE()),2),'-Q',QUARTER(CURDATE()))

ST_Superman

Good catch @rco . If this is a filter tile that would explain the issue!