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

Looker Q&A Looker
,
1

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!

2

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

3

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

4

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