Dynamic filter for dates

[Erica L]

I'm using the Table Filter macro in a dashboard to show dated items from tables on other pages.  I want to use the dynamic filter so that the dates of items shown are from two weeks prior to today, through today.  Is it possible to set this up so that the filter doesn't have to be updated manually?

[Andrey Khaneev _StiltSoft_ · Accepted solution]

Hi @Erica L,

Here you can find dynamic variables for date filters. In your case, you need to set from -2w to today.

[Erica L]

Thank you so much.  I didn't realise that I could type in "today".  

Cheers!

[Andrea Conver]

@Andrey Khaneev _StiltSoft_ Is there an option for previous month? I checked out the link but didn't see that option.

[Andrey Khaneev _StiltSoft_]

Hi @Andrea Conver ,

It is possbile to set -1m. So it is equal to Feb 17 for today. Is it what you need?

[Andrea Conver]

Hi @Andrey Khaneev _StiltSoft_ ,

That's close, but I don't want the query to be the last 30 days. I'd like the report to always be 1st day of the month to the last day of the previous month. So if I run the report today, I'd like the query to run 2/1-2/28. Thanks!

[Andrey Khaneev _StiltSoft_]

It is not possible to do with the Table Filter macro, but you can use the Table Transformer macro with the following query:

SELECT * FROM T1
WHERE T1.'Date' <= ("monthstart" - "1d") AND T1.'Date' >= (("monthstart" - "1d")::Date->setDate(1))

 Here the Date is the name of column with dates to filter.