Hi Good Lookers! I'm trying to create a new dimension that extracts the earliest year from a date field associated with a set of rows identified with a unique ID and applies that year as the value for the dimension for all rows for that unique ID.
Here’s a visual illustration of what I’m trying to do:
ID | Year | New Dimension |
---|---|---|
1 | 2013 | 2013 |
1 | 2014 | 2013 |
1 | 2015 | 2013 |
2 | 2015 | 2015 |
2 | 2016 | 2015 |
Note that for rows with ID 1 the earliest year reported in the ‘Year’ field is 2013. Therefore, in the ‘New Dimension’ field, I want to set its value to 2013 for all rows with ID 1. Similarly, for rows with ID 2, the earliest year reported in 2015. Therefore, I want to set the value for ‘New Dimension’ to 2015 for all rows with ID 2.
Is there a way to accomplish this task within the Looker UI or do I need to build a derived view?
Thanks!
Solved! Go to Solution.
Then it gets tricky, if not impossible. There’s a thread here about doing some hashing
https://community.looker.com/explores-36/max-min-date-in-custom-dimension-11191But if you need it as a dimension I would probably add it in the Data Model
Does it have to be a dimension? If not, you could use a measure and use MIN(YEAR(date_Field)) in the sql paramter of the measure
@Dawid It does because I want to pivot on it.
Then it gets tricky, if not impossible. There’s a thread here about doing some hashing
https://community.looker.com/explores-36/max-min-date-in-custom-dimension-11191But if you need it as a dimension I would probably add it in the Data Model