View the original community article here

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.

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)
   
   
   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.