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.
- 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.
diff_days(${history.created_date},now())
- 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.
1. Last_dateadd_days(${day_diff_1},now())
2. First_dateadd_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.