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

[KonstantinosAnastaso]

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)

SELECT  
    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
TRUE 
[[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!

[Luiggi]

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

[KonstantinosAnastaso]

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?

Best!

Konstantinos

[TonyC]

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

[KonstantinosAnastaso]

Thanks a lot Tony!

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

[KonstantinosAnastaso]

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..

[TonyC]

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)

[KonstantinosAnastaso]

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

[KonstantinosAnastaso]

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

[TonyC]

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

END )

{{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}}