failed to convert value from string to boolean

RajNadar
RajNadar Member
in Magic ETL

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

I try to get dynamic current quarter data filter from dataset

Capture6.JPG
Capture6.JPG

Best Answers

  • ST_Superman
    ST_Superman Domo Employee
    edited April 2023 Answer ✓ 
    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
    rco Domo Employee
    Answer ✓

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

    Randall Oveson <randall.oveson@domo.com>

Answers

  • ST_Superman
    ST_Superman Domo Employee
    edited April 2023 Answer ✓ 
    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
    rco Domo Employee
    Answer ✓

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

    Randall Oveson <randall.oveson@domo.com>

  • ST_Superman
    ST_Superman Domo Employee

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

Categories