Filtering a date to the current date and the first of the month


(jonathan_tao) #1

##Summary
In this post, I will show you how to filter down a date dimension to results that occur on today’s date or the first day of the current month, all from the Explore page. This presents a clean, two-result dimension that is useful for comparison and benchmark analysis.

##Instructions

###Step 1: Create part of the custom filter that filters to current date

Because we will need to use OR logic at some point, we will leverage Looker’s custom filter feature to build our filtering logic. The first step will be writing the code that filters the date to the current date. For this step, we will leverage the trunc_days() custom filter expression and compare our date dimension to the now() value. Your code will look something like this:

trunc_days(${order_items.created_date}) = trunc_days(now())

###Step 2: Create part of the custom filter that filter to the first day of the current month

The next piece is allowing the filter to return results coming from the first of the current month. This requires two-step process: First, we will leverage trunc_months() in a similar fashion as step 1 and additionally enforce that the date must also be the first of the month. To achieve the latter, we leverage the timeframe day_of_month. The resulting code will look something like this:

${order_items.created_day_of_month} = 1 AND trunc_months(${order_items.created_date}) = trunc_months(now())

###Step 3: Put it all together

Assemble the code as follows and add it to your custom filter:

trunc_days(${order_items.created_date}) = trunc_days(now()) 
OR 
(${order_items.created_day_of_month} = 1 AND trunc_months(${order_items.created_date}) = trunc_months(now()))

###Step 4: Do cool things

The resulting filtered date dimension lends itself to being pivoted, which in turn allows you to leverage Looker’s table calculations to perform on-the-fly operations on the resultset, like so:

Note: You will have to make sure that null dates are not filled in. To do this, select “Remove Filled in Dates” from the edit cog for your date dimension