I need to analyse for each employee his working time hours on week basis.
Every week has a different number of working days. These data come from Calendar Table with four dimensions: Week - Week Start Date - Week End Date - Working Days. Employee birthdays are holidays, so the Due Working Days, on the birthday week are decreased by 1.
Time entries are recorded by employees in Harvest.
The outcome must be a Straight Table as per attached screenshot.
Column # - Meaning 1 to 4 - dimensions from Calendar Table 5 - Birthday detects if the employee birthdays falls into that week 6 - Due Weekly Working Days = [Working Days] - Column(1) 7 - Due Weekly Working Hours = Column(2) * 8,40 (comma as decimal separator) 8 - Worked Hours = Sum(timeHours) data from Harvest
If there are noTime Sheet data for a specific week the corresponding row is not shown (see picture 1)
Picture 1
If I want data forced to zero the formula in Column #8 must be changed to = Sum(timeHours) + 0 * Sum({1} timeHours) So I can see all the rows *BUT* the Working Days dimension values are null (see picture 2)