Last tested: Jan 8, 2020
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.
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.
- Verify that you have Custom Fields enabled.
- Open an explore that has the data you are looking for.
- Set up the explore to have your Main Dimension (in this case we are using User ID). Everything else will be custom.
- Create the custom dimension, this will create a number that shows the difference in days between the date we are interested in and today.
- 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.
- Create the table calcs that show the dates.
With the above steps we can easily create a first instance and last instance date per dimension.