Lookup and Rollup returning 0- array/string issue?

[Lauren_Briskin]

Wow, I swear I've asked this AND seen it answered, but can't find it. 

I've created a junction field that contains the sponsor name and a single select field for the year: 

On the table where the sponsors originate, I want to show the MAX year. I've tried lookup and rollup and various suggestions from the forum, but I always get "0." Here's the current set up, based on a suggestion from @kuovonne in a previous thread. Instead of a rollup field, I have a lookup, then a formula to output MAX.

The lookup is great, but you can see the formula always shows "0." The formula I'm using is 

MAX(ARRAYUNIQUE(ARRAYFLATTEN({years Rollup (from junction table)})), ", ")

I've also tried a rollup field:

• Using MAX(Values) gives an error.
• Using ARRAYJOIN(values) gives a list of the numbers, but the forula field still shows 0.

I know I'm missing something obvious, but can't figure out what it is. Thanks!!

[kuovonne]

MAX() works on numbers. Your single select values look like numbers but are not. You can either use a number field for your year, or you can use a formula field to convert your year into a number using VALUE({years}). Then roll up the number.

[Lauren_Briskin]

thanks- would you show me mwhat option two might llook like? 

[Lauren_Briskin]

hi @kuovonne ! Would you mind showing me a sample of this? Thank you!

[Lauren_Briskin]

I'm still struggling with this if anyone has any thoughts!