Creating a field that captures the earliest value from a set of rows

[keith_dowd]

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!

[Dawid]

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

[keith_dowd]

@Dawid It does because I want to pivot on it.

[Dawid · Accepted 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-11191

But if you need it as a dimension I would probably add it in the Data Model