Custom filter error - Logical expression term must evaluate to a boolean

Hi all, I hope you are well!

I am trying to add a filter ( week_year) which I define in the first line (meaning that we don't have that format in the WH)

    CONCAT( cast(year_of_week(dt) as varchar) , '   ',  'W', cast(week_of_year(dt) as varchar)) as week_year,
    dt as date,
    country_cd as country, 
    -- more fields / not relevant to the question here......
FROM table 
WHERE date(dt) > date('2023-01-01') AND
[[AND {{date}}]]
[[AND {{country}}]]
[[AND {{week_year}}]]
GROUP BY 1, 2,3 
ORDER BY dt,country_cd

this is how the table looks like:

So, I get this error :

I also tried: [[AND week_year == {{week_year}} ]] , without success.

Can you please help? thank you!

I suggest you build that field in the DB to use field filters or use the new filters we shipped in 46 which allow you to use manually defined filter values

Thanks a lot Luiggi! Given that it might not be possible to add it as a new field on the DB, can you please give me more details about the filters you mentioned?



When you add a filter to a dashboard you can literally provide what list to show:

Thanks a lot Tony!

I tried that, but it doesn't seem to be working :frowning:

If there is any walkaround for the user to be able to change the date granularity based on a filter (say weekly/monthly) I'd be more than happy to go with that..

What error are you getting in the logs?

the only option would be to use a case statement and a text filter to control what case should be run (either weekly/monthly)

Thanks again Tony! the same as in my original post: "Logical expression term must evaluate to a boolean (actual: varchar)"

@TonyC I hope you are well! Could you elaborate a bit more on this please??

So in your SQL you will need to do something like this:

GROUP BY (CASE {{aggregation}}
WHEN "d" THEN datefield
WHEN "w" THEN week(datefield)
WHEN "m" THEN month(datefield)
ELSE datefield


{{aggregation}} is a text field (required, default "d" ) and 'datefield' being the column where the date is stored in your table

Then you can create a dashboard filter (text filter) and link it to {{aggregation}} in the question so you would be able to dynamically change the sate granularity with the {{aggregation}}