Forum Discussion
Continuous Weeks Tenure
Hello, I have a large dataset where I have shift start and end dates and I need to calculate continuous shifts by weeks. The issue is that there are "breaks" in the weeks so as below I need to calculate the number of weeks for John Smith as 2 weeks for weeks 1 and 2 and then just 1 for week 3. I've spent hours on this and don't want to let it beat me. Thanks Dan
1 Reply
- amitchandak
Super User
Anonymous , Try a column like - A flag
new column =
var _Week =maxx(filter(Table , [Job Ref] =earlier([Job Ref]) && [Name] = earlier([Name]) && [Week] < earlier([Week])),[Week])
return
Switch(True() , isblank(_week) , 1 ,
[Week Of Year] - _week <=1 ,1 ,
0)
