Obtain average duration for each employee and calculate labor duration over several days

[alespooletto]

Hello, 

I have the following table loaded in the script

LOAD
  LABOR_LOG_KEY,
  LABOR_LOG_ACTIVE,
  SubField(LABOR, ',', 2) as LABOR,
  ConvertToLocalTime(Timestamp(LABOR_LOG_START), 'Rome') as LABOR_LOG_START,
  ConvertToLocalTime(Timestamp(LABOR_LOG_END), 'Rome') as LABOR_LOG_END,
  Time(LABOR_LOG_END - LABOR_LOG_START) as Duration,
  Date(Floor(LABOR_LOG_END)) as Date_log,
    If(
      Hour(ConvertToLocalTime(Timestamp(LABOR_LOG_START), 'Rome')) = 10 and Minute(ConvertToLocalTime(Timestamp(LABOR_LOG_START), 'Rome')) <= 15
      OR 
      Hour(ConvertToLocalTime(Timestamp(LABOR_LOG_START), 'Rome')) = 12 and Minute(ConvertToLocalTime(Timestamp(LABOR_LOG_START), 'Rome')) >= 30
      OR
      Hour(ConvertToLocalTime(Timestamp(LABOR_LOG_START), 'Rome')) = 15 and Minute(ConvertToLocalTime(Timestamp(LABOR_LOG_START), 'Rome')) >= 45
      OR
      Hour(ConvertToLocalTime(Timestamp(LABOR_LOG_START), 'Rome')) >= 17 and Minute(ConvertToLocalTime(Timestamp(LABOR_LOG_START), 'Rome')) >= 30,
      1, 
      0
  ) as Breaks,
    If(
      Time(LABOR_LOG_END - LABOR_LOG_START) >= 0.3,
      1, 
      0
    ) as LongShift
FROM Table
where Year(Date(Floor(LABOR_LOG_END))) >= 2023
  and WildMatch(LABOR,'*IT*');

I want to obtain a final measure that tells me the average hours worked by the employee, but within all the Calendar available. 

The Duration column is currently the column I use for getting the duration. There are a couple issues I would like some advice on:

Sometimes the LABOR_LOG_START is in one day, and the END in another day, and I was only able to do a Flag (LongShifts) to remove them and make the count easier, I'm not sure how else to handle it. 

The other is regarding the average. Right now, I am using a formula over the sheets page, but I would prefer to obtain it directly from the load editor script, and the formula I am using can't be used here: 

AvG(Aggr({< Weekday = {'Lu', 'Ma', 'Me', 'Gi','Ve'}, LABOR=>} =IF( LongShift = 0 , Sum(Duration)), Date, LABOR))

I am not sure if there's another solution to this that doesn't involve creating a new aggregated table for it.

[LRuCelver]

Not the answer you wanted, but I cleaned up your code a bit using a preceding load:

NoConcatenate Load
	*,
    If(
        Hour(LABOR_LOG_START) = 10 and Minute(LABOR_LOG_START) <= 15
        OR 
        Hour(LABOR_LOG_START) = 12 and Minute(LABOR_LOG_START) >= 30
        OR
        Hour(LABOR_LOG_START) = 15 and Minute(LABOR_LOG_START) >= 45
        OR
        Hour(LABOR_LOG_START) >= 17 and Minute(LABOR_LOG_START) >= 30,
        1, 
        0
  	) as Breaks,
    If(Duration >= 0.3, 1, 0) as LongShift;
Load
    LABOR_LOG_KEY,
    LABOR_LOG_ACTIVE,
    SubField(LABOR, ',', 2) as LABOR,
    ConvertToLocalTime(Timestamp(LABOR_LOG_START), 'Rome') as LABOR_LOG_START,
    ConvertToLocalTime(Timestamp(LABOR_LOG_END), 'Rome') as LABOR_LOG_END,
  	Time(LABOR_LOG_END - LABOR_LOG_START) as Duration,
    Date(Floor(LABOR_LOG_END)) as Date_log	//	You get the date before you convert to local time. If this is incorrect, move it to the segment above.
From [lib://SEWQVD_Streams_ME_Global_Prov_4000/AssemblyData/LABOR_LOG.qvd](qvd)
Where Year(Date(Floor(LABOR_LOG_END))) >= 2023
  and WildMatch(LABOR,'*IT*');

[alespooletto]

Well, thank you! Any help is appreciated 😄