Max number of programs running concurrently

[air0storm]

Hi community,

At a loss on how to do this calculation elegantly.

My data looks like this.

Manager	Program	Start Date	End Date
Smith	A	1994	2010
Jones	B	2014	2020
Jones	C	2019	2025
Davies	D	2000	2001

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 solution]

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:

[air0storm]

Thank you. That "generate rows" tool was what I was missing. I was imagining having to manually add 50 columns or append them as fields which seemed far too laborious