Hey, All!
I've been playing around with alteryx for a few months now and have read some books+watched a decent amount of tutorials, but sometimes as you all know this doesn't prepare you for everything.
Without sharing a dummy file (and I can if absolutely needed, it would just take a bit to recreate whats needed), I need a date formula that can do the following:
I have 6 columns of data joined between two data sources, and my join seems to bring in the appropriate employees given my filters etc.
Employee ID, Name, # Of Weeks, Week-End-Date, Pay_end_Dt, PTO HOURs
I need a formula that will look at each row and check the [Week-End-Date] and use this as the base to start with. Using [Week-End-Date] we need to sum the total number of [PTO Hours] going back the [# Of weeks] listed and also taking into consideration [Pay_End_Dt].
For example-
Employee 1 has as [week end date] of 2024-01-27, a [# of weeks] of 2, and several random [Pay_end_dt] weeks worth of data. He also has [PTO hours] as well for those random weeks.
Depending on his [week-end-date] will determine where this formula starts, and if his [# of weeks] is 2, then we need to sum up the [PTO hours] starting with 2024-01-27 [Week-end-date] and going back one week....The caveat here is that it matters what [Pay_end_dt]s are available. So if the Employee only has say 2024-01-27 [Pay-end-dt] data then you would only calc that week, if he has a [pay-end-dt] of 2024-01-27 and also 2024-01-20, you would sum up those two weeks worth of [PTO hours].
If the employee has NO [Pay_end_dt]s that are within the [# of weeks] from the [Week-end-date], it would just return 0 regardless of how many PTO hours he has we only care about the # of weeks from the week end date, and the weeks ONLY lining up with Pay end dates to get the sum.
Any help or guidance with this would be greatly appreciated.
