Max number of programs running concurrently

Question

Hi community,

At a loss on how to do this calculation elegantly.

My data looks like this.

ManagerProgramStart DateEnd DateSmithA19942010JonesB20142020JonesC20192025DaviesD20002001

I want to ask "what was the maximum number of programs each manager had going on in any any given year, and which year(s) was that?" So in this case, for Jones it would be 2 in 2019 and 2020, because B & C were both running then.

The only way I can think of to do it is to add columns to the right for every single year and ask "was active in this year?" But I have a lot of years so that gets messy quickly.

Thanks!

Emil_Kos · Accepted Answer

Hi @air0storm,

I would use the logic suggested by you. Creating a new line for each year is one of the ways to achieve these results. As the second step, we can just use the summarize tool.

Output: