Return to site

Custom Date Filters in Looker

By Bob Vermeulen

· Looker,Data,Data Visualization,Dashboard

Usually the out-of-the-box date filtering capabilities of Looker are 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 as well as custom query filters are two powerful features that can incorporate complex logic for the reporting. In this article we illustrate how we took this approach to meet some custom date filtering requirements from one of our clients. Here is what they needed for a specific report:

  • The report should show month-to-date (MTD).
  • Saturdays and Sunday should be backdated to the previous Friday
  • When viewed on Tuesday through Friday, the report should show data through the previous day.
  • When viewed on a Monday, the report needs to show data through the previous Friday.
  • On the first day of the month, the report should show the previous month in total.

How does this help the end user?

In 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 on-going without manual intervention. Often this logic gets complex and would be difficult to create using the standard filters available, especially for non-developers.

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, which as you'll see in a moment 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

extract_years(${date_daily_sales_dim.report_year}) = extract_years(now())-1

When it is not Mondays the If statement uses the following to grab the previous day and prior days of the month:

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

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

extract_years(${date_daily_sales_dim.report_year}) = extract_years(now())-1

))

Here is the entire custom filter:

if(

 

extract_days(now()) = 1,

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

extract_years(${date_daily_sales_dim.report_year}) = extract_years(now())-1

,

if(

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

,

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

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

extract_years(${date_daily_sales_dim.report_year}) = extract_years(now())-1

,

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

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

extract_years(${date_daily_sales_dim.report_year}) = extract_years(now())-1

))

Conclusion:

Using a combination of Looker Functions, SQL Date Functions, and If statements within the Looker custom filter allows developers to handle complex reporting requirements for dynamic date filtering. The example above incorporates all of the above manipulations to create a report that is hands off for the user and is easily adapted to meet additional requirements for different reports. The flexibility is only limited by the imagination of the developer.

Data Clymer is a premier boutique consulting firm specializing in data culture transformation. Our proven methodology includes full data stack implementation, data democratization, custom training, and analytics to enable data-driven decisions across the organization. We have curated a set of best practices from our deep expertise in Looker, Tableau, Snowflake, Redshift, BigQuery, Panoply, Matillon, DBT, and Fivetran. If you need professional help, contact us at hello@dataclymer.com, or on our Website or Linkedin.

All Posts
×

Almost done…

We just sent you an email. Please click the link in the email to confirm your subscription!

OK