identifying the next gateway in line based on dates

[ZoeM]

Hello Alteryx Community.

I am trying to create a new field that identifies which milestone is next in a series. In the attached sample data, I have 4 milestones and they are ordered by Start Date. 

How can I identify them/classify them as 'Achieved', 'Next', 'Future' so that as the End Date is populated they change classification?

Hope that makes sense.

Thanks!

[fmvizcaino · Accepted solution]

Hi @ZoeM ,

Attached is an example showing how to get that done! I'm using multi-row formula tool to be able to look to records above and insert the right milestone.

Let me know if that works for you.

Best,

Fernando V.

[ZoeM]

Fernando! 

I love it, but I have additional variable to throw in.

If the data is mixed (simple sample added), is there a way to identify by Model then check the Stage and see the dates order to determine the Milestone field?

[ZoeM]

Never mind Fernando!

It works like a charm, I didn't realize that you were grouping by Model!

I love it!