Multi-row formula to capture other "n" rows

[john-colarusso]

Hi,

I am trying to project new hires' contribution to our overall weekly hours of supply. I have one input which is estimated hires per week by state, and one input which is the typical growth in contribution of one new hire by week

I have two questions/issues:

1. I am currently using a multi-row formula to brute force calculate the contribution of cohort ( [Row-n:Activations]*[Row-n:Week_n hours] ). However, this is a very manual way to do it, and only works if I type in enough of those cohort contribution statements to cover the max number of rows per grouping. Otherwise, I lose the contributions of the cohorts that fall outside that range of rows
2. I want to assign a different growth assumption after 12 weeks

I am pretty stuck, so any suggestion is welcome

Thank you in advance for the help!

John

[AngelosPachis]

Hi @john-colarusso ,

Regarding issue A I think you are reaching that limitation because your weekly hour contribution is in different columns. I am not sure if you have to cross-tab the data in the first place. If you don't, then for each state, you will have activations and week # in different columns, and you could use a formula tool to find their product. Then you could use a summarise tool to sum those values, which will return the total hour contribution per state.

Regarding issue B, I am not sure where that different growth assumption is assigned or what should it do to answer your question. If you take some more time to document your workflow and then share it again, then I think it will be easier for people outside that industry to assist you 

(Quick question, in your generate rows tool, your condition expression was set to [Weeks for onboarding] <= "2022-12-31", shouldn't that be 2020-12-31, since you are counting the weeks to end of year (2020) in the formula tool before?)

Regards,

Angelos