6 Replies Latest reply on Jun 20, 2019 9:37 AM by Ken Flerlage

# Find the year a value first occurs

Hi,

I'm struggling to come up with a calculate field to determine the first time a "1" appears in the second column for each country listed in the first column. Where would I begin? I'm assuming I'd need to segment by country, but I don't know how.

• ###### 1. Re: Find the year a value first occurs

Try the following calculated field:

Year of First 1

// Get the year the first 1 appears for the country.

{FIXED [Country]: MIN(IIF([Treaty of Tlatelolco]=1, [Year],NULL))}

See attached.

1 of 1 people found this helpful
• ###### 2. Re: Find the year a value first occurs

Thank you so much!!!!

• ###### 3. Re: Find the year a value first occurs

Ken Flerlage

How would I change it so if the value is 2 in the year 1967, to set the output to 1967? Here's what I tried:

// Get the year the first 1 appears for the country.

{FIXED [Country]:

IF [Treaty of Tlatelolco]=2 AND [Year]=1967

THEN 1967

ELSE(IIF([Treaty of Tlatelolco]=1, [Year],NULL)) END}

• ###### 4. Re: Find the year a value first occurs

That looks like it would work.

• ###### 5. Re: Find the year a value first occurs

I'm getting an "result of a detail expression must be an aggregate" error returned.

• ###### 6. Re: Find the year a value first occurs

Oh, right, the second part of an LOD needs to do an aggregation, so try this:

// Get the year the first 1 appears for the country.

{FIXED [Country]:

MIN(IF [Treaty of Tlatelolco]=2 AND [Year]=1967

THEN 1967

ELSE(IIF([Treaty of Tlatelolco]=1, [Year],NULL)) END)}