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!
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?
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?
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.
We can replicate that logic, though, by doing something like this:
Since we also have a
timetimeframe, we can filter on that field as well, and select the
on or afterlogic in one filter & the
beforelogic 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.
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!
I GOT IT!!!
Thanks for your help!
Yay! Nice work 😄