Metric "per timeframe" that listen to the dashboard filter

dashboards
lookml

(Jonathan Tao) #1

Example Problem

Pretend you have a measure, like Sessions. It aggregates up on a table that is granular to each session occurrence that has, among other things, a date attribute. You’d like to express this information as a single value tile called “Sessions per Week”, and have it listen to a dashboard date filter such that any date range can influence the number of weeks used in the denominator.

Solution

We’re going to use a couple of concepts to accomplish this. Below are some helpful links to review before jumping in:

Step 1: Create your date filter

Begin in LookML By building a standard filter-only field of type date.

  filter: date_filter {
    type: date
    convert_tz: no
  }

This date filter is the core metric that will perform the following tasks:

  • Filter on date attrbiutes based on dashboard date filter
  • Calculate the number of weeks (or whichever timeframe you prefer) within the dashboard date filter range, whatever the end-user chooses.

Step 2: Tie your new date filter to your data’s date attribute

We need to use this new date filter to act just like we were filtering directly by the date attribute in our data. To do this, we create a yesno dimension is_in_date_filter, like so.

  dimension: is_in_date_filter {
    type: yesno
    sql: {% date_start date_filter %} <= ${page_view_date} 
         AND ${page_view_date} < {% date_end date_filter %};;
  }

Step 3: Build your timeframe calculator

Now we need to build SQL logic that calculates the number of weeks in the range of the new date filter. Leveraging your dialect’s DATEDIFF function, you can do something like so (below is a postgres implementation, with a rounding up factor).

  dimension: num_weeks_in_date_filter {
    type: number
    hidden: yes
    sql: CEILING(DATE_PART('day', {% date_end date_filter %} - {% date_start date_filter %})/7);;
  }  

Step 4: Build your dashboard tile and connect the dashboard filter

We’re in the home stretch. Now you can build your tile in your Explore like so…

…And attach it to the dashboard filter like this.

That’s it! Now your end users can choose virtually any date filter range while having a dynamic metric based on their filter selection.