Custom Date Filters in Looker

Looker’s out-of-the-box date filtering capabilities are usually sufficient—but every once in a while, end users need some advanced date logic for an optimal experience. This is where the power of Looker’s programmatic platform really shines. Specifically, custom date dimensions and custom query filters are two powerful features that can incorporate complex logic for the reporting.

In this article, we illustrate how we used custom date dimensions and custom query filters to meet some custom date filtering requirements from one of our clients.

Related: 3 strategies to improve Looker dashboard performance

How do custom date filters help the end user?

Creating dynamic filters for the purposes of MTD summaries relieves the user of having to change the dates used in a report (a “look”). Once set properly, the report will meet the requirements ongoing without manual intervention. Often this logic gets complex and would be difficult to create using the standard filters available, especially for non-developers.

Here is what our client needed for a specific report:

Show month-to-date (MTD).
Backdate Saturdays and Sunday to the previous Friday.
When viewed on Tuesday through Friday, show data through the previous day.
When viewed on a Monday, show data through the previous Friday.
On the first day of the month, show the previous month in total.

The solution for this example:

The first step is backdating Saturdays and Sundays to the previous Friday. Fortunately Looker gives us the ability to use day of week indexes defined in the date dimension. As you can see, this makes the next calculation much easier:

We can then use that day_of_week_index in a 2nd date dimension that incorporates with a case /when statement that takes two days off the posting date for Sundays and one day off for Saturdays. Remember that in Looker is configured by default to have weeks start on Sundays, the day_of_week_index outputs a 0 for Sunday, 1 for Monday through 6 for Saturday:

Now we can refer to our new posting_no_weekend date dimension in a “Custom Filter” while exploring the data.

To build the logic needed in this custom filter, we start with logic to show the full prior month instead of the current month if the current day is the first day of the month. To do this, we use an IF statement combined with the extract_days function that returns the day of month with a current day function now():

if(extract_days(now()) = 1,

When that is true (current day = first day of month), Our next statement when it is the first day of the month defines the month previous and the current year:
${gl_entry.posting_month_num} = extract_months(now())-1 AND

extract_years(${gl_entry.posting_year}) = extract_years(now())-1

When the current day is not the first day of the month, we then need to figure out what day of the week at which to complete the MTD window. If the current day is a Monday then we need to view MTD through the previous Friday. To handle this, we embed another If statement to check if it is Monday using another function that compares the current day to a preset day of week function:

if(mod(round(diff_days(date(2008,01,01), now()), 0) + 1, 7) = 0

For Mondays we add -2 days to the current day to go back to Friday:

${date_daily_sales_dim.report_month_num} = extract_months(now()) AND

${date_daily_sales_dim.report_day_of_month} < extract_days(now())-2 AND