Stuck on filtering step

low_priority
filter
done
reply
(Kristin Sergi) #1

Hello,

Our client case is:
Say today (April 10th), the report should have courses(data) listed that are expired within next 60 days.
On May 10th, they’d want auto report to send with courses that would expire within 60 days after May 10th.
Same for subsequent months.

My guess is I need to filter the data by: “N days from now” but I’m struggling with filtering the data that way.

I tried the steps here:

When I do step 2, no other dialog box pops up and doesn’t seem to be doing anything! How do I even get to step 3, or is step 3 even needed? I’m trying to filter on a column with expiration dates and I only want to see courses that expire within 60 days!

Thank you!

0 Likes

(Izzy) #2

Hey Kristin!

I made a quick little gif in hopes that it makes the process a bit clearer.
I selected 1 month from now, but in the gif you can see where the “days from now” option is, and you could choose 60 days for that option. (My query returns no results because I don’t have any future data).

Does that gif make it a bit more clear?

0 Likes

(Kristin Sergi) #3

Hello!

I actually would need results from now until 60 days from now. So for example, April 10, 11, 12, etc until 60 days from today. Does that make sense? How would I accomplish this?

Thanks!

0 Likes

(Izzy) #4

Aha, then yes, this does get a bit more complicated.

There’s 2 ways to do this: One is easier, but a bit clunky, and the other is more streamlined, but a bit harder.

  1. Use 2 dates to filter on. Since the default date filter addition defaults to “OR” logic instead of “AND”, we can’t just do this: which would be ideal.
    We can replicate that logic, though, by doing something like this:

    Since we also have a time timeframe, we can filter on that field as well, and select the on or after logic in one filter & the before logic in the other. This gives us the “AND” logic we’re looking for and should work.

If you don’t have another time frame available and don’t want to create one (you could just create another field in the LookML with exactly the same definition and call it date_filter or something) then you can use a feature called Custom Filters.

  1. There’s a checkbox in the filters section called “Custom Filters” and when you pop it open, it brings up a text entry area. You can use Looker Expressions (https://docs.looker.com/exploring-data/creating-looker-expressions/looker-functions-and-operators) in that section, which means you can create some more powerful filters. For this case, something like:
${view_name.date_field_name} > now() AND ${view_name.date_field_name} < add_days(60,now())

would work excellently, replacing view_name.date_field_name with the actual names.

I realize this is a bit complicated! You should definitely add a vote to this feature request: Relative Range filter
which would make this process super easy.

Happy to walk through this one more if you need!

0 Likes

(Kristin Sergi) #5

Izzy,

I GOT IT!!!

Thanks for your help!

1 Like

(Izzy) #6

Yay! Nice work :smile:

0 Likes