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!
Solved! Go to 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:
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