Compare custom Date ranges, or any custom tiers. Enable end users to run these queries at will

How to allow end users to compare their custom date ranges in the Explores in just one query? Comparing 2 or more standard date intervals such as week, month is straightforward thanks to Looker’s time filters.

However our teams may also require custom date ranges. We release a new feature or launch a new marketing campaign at irregular intervals. We want to confirm their impact as soon as possible, and we keep track daily of the statistical significance of variations observed. This may result in comparing the 11 days before the change with the 18 days following.

In Looker an end user with no access to Developer mode can easily apply some types of filters:

• filter 1 period through a Date range filter.


• compare several values a dimension can take to the rest of population: after Data team developers have created the filter and dimension as specified by the Looker team here, any end user can compare measures for 2 values of one dimension, eg ‘Brand’, in one step. They simply select ‘Brand’ as filter and input 1 or more brand names in the filter text box.
  
  They can do so at will - with no need for a Data team member to create any sql (or LookML sql_case) statements.

I wanted to likewise enable an end user to compare their set of metrics not for 2 values of a string dimension, but for any non-standard custom date intervals they want. How to obtain a table of KPIs comparing date ranges « Period starting from release to today » vs. « N days before the release » ? Would there be filters end users can define every time with no ongoing coding from the Data team ?

I found that the Rest of Population analysis suggested by Looker team here1 also enabled the end user to do just that, with no ongoing help from the Data team.

1. Create a filter-only dimension for the dimension, suggesting the intended date or numerical dimension. Eg a filter-only « order date », « time on site », « order value » (in addition to the initial dimension)


2. Create a comparitor dimension for the ‘Defined range’ vs. ‘Rest of range’


3. Now, every time the end user wants to analyze a new custom interval, they can do it in 2 steps.

These 2 steps for the end user to follow:

1. Using the initial dimension eg ‘order_date’ as Filter, the user sets min and max matching the min and max of the join of the 2 intervals. Eg « Date is after (date 12 days before the release date) but before today », or « Time on site is more than 5 seconds », or « Order value is more than 0 but less than 4,000 (anomalous value transactions)».


2. Then, using the Filter-only matching field, they can run queries on any one “Before” (or “Less Than”) interval against the other “After” (or “Equal or More Than”) interval, because this now equals the rest of the range = Rest of Population.

  - filter: order_period_select

    label: 'Compare Date ranges - Orders made Before / After...'  

    type: date

    suggest_dimension: order_date

   

  - dimension: order_date_comparitor

    label: 'Compare Date ranges - Orders made Before / After...'

    sql: |

      CASE WHEN {% condition order_period_select %} ${order_date} {% endcondition %}

      THEN 'Date range set as \'before\''

      ELSE 'Date range set as \'after\''

      END

      





  - filter: order_value_select

    label: 'Compare Value ranges - Orders value >= or < to...'  

    type: number

    suggest_dimension: order_value

   

  - dimension: order_value_comparitor

    label: 'Compare Value ranges - Orders value >= or < to...'

    sql: |

      CASE WHEN {% condition order_value_select %} ${order_value} {% endcondition %}

      THEN 'Order value < set threshold'

      ELSE 'Order value >= set threshold'

      END

For instance, end users would compare « visit date = post-release to today, against visit Date = 10 days before release to release date ». The Data team can sit back and relax.

Would there be a way to allow end users to do any comparison of 3 or more custom interval dimensions, appearing side by side in the same Explore query? (Running such queries is not requested as often!).

Beyond this, any comments related to Time To Significance would be great. Worth a dedicated post?