Exclude a Day from Dataset

moebe
Participant V

how can I filter out a single date in an Explore.
So get all dates except one?

0 9 2,281
9 REPLIES 9

Dawid
Participant V

I would use a Custom Filter to do that

moebe
Participant V

thanks!
that’s how I did it now.
But it isn’t “customer-friendly” and suitable for self-service, right?
Isn’t there an easier way?

Dawid
Participant V

Do you want to exclude it permanently from your view? Is your view built using a PDT or sql_table_name ?

moebe
Participant V

No, not from the View.
But think about it: You have a progress visualization.
A single day has an unusual spike.
That “screws up” your whole visual.
Now you want to quickly filter that out (with exclude or something).
Do I really need to tell my client
"You can include or exclude every dimension and measure by filter.Only with a date this is not possible!
?
Not sooo nice.

Dawid
Participant V

I’m guessing the spike is only on one measure, hence you can’t exclude it. In this case I can’t see any other way of excluding it rather than Custom Filter. The date/time filter doesn’t have is not <specific date>

image

which would be useful in your case

bens1
Participant V

What you could do is make a yesno type parameter, and use Liquid to add a conditional filter in your date dimension which would exclude certain date parts (i.e. day of week = 7, etc.), depending on whether that parameter is in the query. Hope that helps!

Adding up my 2 cents on top of @bens1.

You also use a combination of type: date parameter and sql_always_where to exclude the selected date but that would be for one date only.

If you want to push that further you can create a type: string parameter that accepts any value.
So your business users can enter dates separated by commas which you can filter out using the sql_always_where parameter at the explore level

moebe
Participant V

Hello everybody
thanks a lot for your help and all the good workarounds.
I will get further with that for now.

Unfortunately I don’t find any of the workarounds really good.
Actually it shouldn’t be difficult for Looker to make an exclude filter possible, just like with dimensions.

But this is not your problem. Thanks again!
Have a nice weekend to you all.

I had the same challenge recently. I was willing to compare Feb-21 vs Feb-20 results, but in 2020 Feb had 29 days so the comparison was not like-for-like.

The best solution I came up with was to use the matches (advance) filter option with the following setup: before 2020/02/29, after 2020/03/01

Another option, for those who’re SQL fans it would be to add a Custom Filter where table.date != date(2020,02,29)

02759b3f-f2d3-4a78-a1d3-f583ca0570e5.png
Top Labels in this Space
Top Solution Authors