Special date filter on an explore

[bromy]

Hello,

I’m looker to filter a date attribut on an explore to get a specific range.

Here is an example to explain what I’m looking for :

Today is the 19th of april.

I’d like to filter to get the following range ; 1st march to 19th march.

That means from the first day of the month one month ago, to the day one month ago.

Is that possible ?

Thank you !

[Dawid]

It seems that you’re looking to get Month-to-date type of measurement. With Looker date/datetime filters you would have to change it every day. 

The best way would be to create a siimple yes/no dimension called “is_month_to_date” and then use SQL dialect to calculate whether a record falls within the range you specified.

A more comprehensive approach would require building a so called calendar table, that could have the calculations already done and joining it to an explore would give you the information about MTD, WTD, or YTD straight away.

[IanT]

if its just a filter you want in the explore then set it to “is in the past” “2” “months”, this will filter to the current month which is not completed and the prior full month.

[bromy]

if its just a filter you want in the explore then set it to “is in the past” “2” “months”, this will filter to the current month which is not completed and the prior full month.

Thank you for the answer,

Yes but I’d like something like “is in the past “2” months” minus “1 complete month”. I guess it’s not possible with filters in the explore ?

[Dawid]

Correct, the default filters on date/datetime fields that Looker gives us do not have any XTD capability

[IanT]

Apologies I misread the original post and missed the enddate was last month.

If you can accept not perfect then this is close.