Custom Filters: Exactly 1 Month Ago

Looker will not be updating this content, nor guarantees that everything is up-to-date. 

The Problem

When analyzing data, you might want to see a month of data ending today. For example, if today is Feb 6, 2019, that means I want to see results spanning the range from Jan 7, 2019 through Feb 6, 2019.

This is a challenge with basic filters: "the past 1 month" refers to February and "the past 1 complete month" refers to January. We can look into the Advanced filter box and try "30 days ago for 30 days," which gets close, but is not dynamic based on the number of days in a month.

The Solution

Custom filters give us more flexibility by letting us reference now(), a timestamp for the current date in our filter condition.

Given this, we can write a custom expression using Looker filter expressions that catches all dates a month before the current day of month, and catches all dates this month, before and on the current day of month. The expression is below:

  (extract_days(${history.created_date}) > extract_days(now())
AND extract_months(${history.created_date}) = extract_months(add_months(-1, now())))
OR
(extract_days(${history.created_date}) <= extract_days(now())
AND extract_months(${history.created_date}) = extract_months(now()))

The Explanation

This filter expression reads "Either the day of month of the data is later than the current day of month, and the month of the data is last month,
or
the day of month of the data is earlier than the current day of month, and the month of the data is this month."

The extract_days function returns the day of month number, while the extract_month function returns the month number. More functions and operators to adapt this expression to other timeframes are available in the Looker Functions and Operators documentation.

Version history
Last update:
‎06-23-2022 09:29 AM
Updated by: