How to create a First and Last date using Custom Fields

Knowledge Drop

Last tested: Jan 8, 2020
 

The Problem

Say you want to see a user's first and last query dates. This could be pretty difficult because it is in system_activity so you can't edit the lookml, and you can't easily min/max dates.

A Solution

We will use a combination of Custom Dimensions, Custom Measure and Table Calcs to achieve this.
Here is a picture of what that looks like.

Explore_User.png

  1. Verify that you have Custom Fields enabled.
  2. Open an explore that has the data you are looking for.
  3. Set up the explore to have your Main Dimension (in this case we are using User ID). Everything else will be custom.
  4. Create the custom dimension, this will create a number that shows the difference in days between the date we are interested in and today.
    diff_days(${history.created_date},now())
  5. Create the Custom Measures.
    1. Day_diff_1 (this is the last_day)
    Explore_User-2.png

    2. Day_diff_2 (this is the first day)
    Same as above, just change measure type from min to max.
  6. Create the table calcs that show the dates.
    1. Last_date
    add_days(${day_diff_1},now())
    2. First_date
    add_days(${day_diff_2}*-1,now())

With the above steps we can easily create a first instance and last instance date per dimension.

This content is subject to limited support.                

Version history
Last update:
‎07-07-2021 01:14 PM
Updated by: