failed to convert value from string to boolean

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

I try to get dynamic current quarter data filter from dataset

Best Answers

  • ST_-Superman-_
    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.


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • 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()))

Answers

  • ST_-Superman-_
    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.


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • 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()))

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


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman