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


#1

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?


#2

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 :slight_smile:


#3

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.


#4

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

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


#5

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


#6

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.