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.
Jun 16, 2019 3:11 PM
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.
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}
Jun 20, 2019 7:19 AM
That looks like it would work.
I'm getting an "result of a detail expression must be an aggregate" error returned.
Jun 20, 2019 9:37 AM
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)}