How do I set a variable filter default that moves relative to now?

[gauravvohra]

SQL is like:

SELECT * FROM table
WHERE week = {{week}}

I want this to be showing the most relevant, "current week", every time I look at the dashboard, as time moves on.

The normal way to do this would be to set the default value for {{week}} as "current_date()" (or some variation). However, it looks like I can only set {{week}}'s default to a static date, which isn't the most useful.

Am I missing something, or is this a missing feature?

Is there a different feature I could use for something like this?

[josi.aranda]

hi gaurav,

i would use something like this:
SELECT * FROM table
WHERE 1
[[ and week = {{week}} #]] current_week()

or

SELECT * FROM table
WHERE 1
[[ and date= {{date}} #]] date_sub(date(now()),interval 7 day)

(it’s basically the same thing)

Notice i use hashtag(#) to make the condition not applicable when the parameter is set.

Hope this help

[AndrewMBaines]

If you were trying to do something like this in a data warehouse, you’d just create a date table and update it daily (easy enough).
Table would have columns of date, month, year, day of week. isLastWeek, isThisWeek, isYesterday, quarter etc etc
Contains dates for the last n years where n depends upon your data.

[EvaS]

Hi,
in my variables I defined the filter as "relative date" - then it works:

I always set last 30 days as default value and it works very well
Hope that helps, cheers, Eva

[gauravvohra]

Thank you Josi! This worked really well. I went with:

[[ DATE_ADD({{week}}, INTERVAL - WEEKDAY({{week}}) DAY) #]] DATE_ADD(CURDATE(), INTERVAL - WEEKDAY(CURDATE()) DAY)

So that the date (if provided, or if today) always flattens to that week’s Monday date.

I should add for future readers that this only works if you ensure that {{week}} is not a required parameter (the UI makes it quite confusing to establish that this is the case).

[HayatBJJ]

Hi,
I’m using mongodb so I need the query equivalent of this:
" select count(*) from products where {{category}} “.
I don’t even know if it’s possible or not.
I tried this one :
{
“$group”:{”_id":{“stage”:"$stage",“project”:"$projectName"},
“count”:{"$sum":1}
}
},{"$unwind":"$_id"},{ “$project” : {
“_id”:0, “stage”:"$_id.stage",“project”:"$_id.project",“count”:1
}},{"$match":{“project”:"$$category"}}
But it didn’t work.

[ampali]

@josi.aranda @gauravvohra

I am also trying to do a similar thing in v0.32.4 but getting an error:
mismatched input '#'. Expecting:

This syntax works fine when no value is provided in the filter but the above error comes as soon as I enter values in the filter.

Any suggestions?

[flamber]

@ampali
You’re using Presto, so you need to use the comment syntax for your database, which I think is two dashes (--) instead of a hash (#)

[omerfaruk]

i do not understand this solution can somebody tell me how can i use this in sql server.

[flamber]

@omerfaruk SQL Server uses -- for comment syntax:
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/comment-transact-sql?view=sql-server-ver15
So instead of using # you should use --

[omerfaruk]

i know the comment but it comments all line and this code does not work …
i do not understand the logic

select * from Mytable
where 1=1 [[AND cast(date as date) = {{datevar}}--]] cast(getDate() As Date)

[flamber]

@omerfaruk That’s because you should do this instead:

select * from Mytable
where 1=1 AND [[cast(date as date) = {{datevar}} --]] cast(getDate() As Date)

[omerfaruk]

Hello i am expecting if i do not choose any date filter it show getDate() filter
Am i wrong ?

But in this query if i did not choose any date filter it comments everything. and query does not work ..

[flamber]

@omerfaruk But which column is this supposed to check against? cast(getDate() As Date)
Here’s what you do. Remove the Optional Clause. Make sure that the query works as expected and then add the Optional Clause back.
Providing the real query is much easier, since your example would never have worked.
I would guess you’re trying to do this:

select * from Mytable
where 1=1 AND cast(tarih as date) = [[{{datevar}} --]] cast(getDate() As Date)

[omerfaruk]

thank you it helps a lot