Solved

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

  • 14 December 2021
  • 3 replies
  • 55 views

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!

icon

Best answer by Dawid 15 December 2021, 09:01

View original

This topic has been closed for comments

3 replies

Userlevel 7
Badge +1

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.

Userlevel 7
Badge +1

Then it gets tricky, if not impossible. There’s a thread here about doing some hashing 

 

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