Calculating length of time without a break

Question & Answer
, , ,
1

Hi All,

I have a somewhat complicated query which I can’t quite get my head round in Insights. I have data which shows the dates workers ({Agency Worker}) have filled shifts ({Shift Start Date}) and I need to calculate the length of time they have worked.

However, the shifts can’t be more than 6 weeks apart. For example, if shifts A (31/01/2021) and B (04/04/2021) are 2 consecutive shifts this worker has filled but are more than 6 weeks apart then the new ‘first shift’ date with which to calculate the length of service has to become Shift B (04/04/2021).

Is calculating this in Quicksights a possibility?

Thanks
Jake

2

I think I have two calculated fields that might help for now. I’ll need to do more looking to complete the process, but maybe this can help.

date_diff_weeks from shifts = dateDiff(lag({Shift Start Date},[{Shift Start Date} ASC],1,[{Agency Worker}]),firstValue({Shift Start Date},[{Shift Start Date} ASC],[{Agency Worker},{Shift Start Date}]),‘WK’)

That will get you the amount of time from each shift.

total_time_worked = dateDiff(firstValue({Shift Start Date},[{Shift Start Date} ASC],[{Agency Worker}]),lastValue({Shift Start Date},[{Shift Start Date} ASC],[{Agency Worker}]),‘DD’)

This will get you the total time worked without taking into consideration of the 6 week break period.

Now, we need to combine them together. So that you can partition by the “New Shifts”

partition_field_not_complete = ifelse(dateDiff(lag({Shift Start Date},[{Shift Start Date} ASC],1,[{Agency Worker}]),firstValue({Shift Start Date},[{Shift Start Date} ASC],[{Agency Worker},{Shift Start Date}]),‘WK’)>6,6,NULL)

This is what I have now. I need to figure out how to relay any date from these next 6 weeks to be the new shift.

However, once I get that then you should be able to complete it with this calculated field.

final_not_complete = dateDiff(firstValue({Shift Start Date},[{Shift Start Date} ASC],[{Agency Worker},{partition by 6 weeks}]),lastValue({Shift Start Date},[{Shift Start Date} ASC],[{Agency Worker},{partition by 6 weeks}]),‘DD’)

Wanted to give an update and hopefully this can help for now / see if anyone else can finalize it.

2 Likes
3

Hi @JakeMcK1995 , did Max’s solution work for you? I am marking his reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!