Display Sum of Missing Time

[ciaran_mcgowan]

Hi all,

I have been trying to solve an issue for a while now was wondering if anyone in the community have come across something similar in the past. I have a client would like to get a total amount of hours per Month that are not being logged by employees and have the resulting number displayed in a text box. The table that contains what each person does in a day is called TIME and each person needs to have logged a minimum of 8 hours per day. To see what the result should be, I am using the following expression:

(COUNT({<WeekDay -= {"Sat","Sun"}>} WeekDay)*8) * (COUNT( EMPLOYEE.Active))

It finds out the number of weekdays in a month, converts that into hours (*8) and then multiplies it by the number of people working at the company (EMPLOYEE table). The problem is I can't just compare that to sum of hours worked in that month (below) as certain values must be ignored:

Sum({<WeekDay -= {"Sat","Sun"} TIME.Hours)

In the TIME table, each employee must have at least 8 hours per day, but can have multiple entries for each day (each represents a different Job). Some days, employees can work well over 8 hours so that would also affect the numbers if I used the expression above. A typical TIME table would look like this:

ID, DATE, EMPLOYEENAME, JOBNAME, HOURS

1, 2013-11-2, Bender, Job1, 3

2, 2013-11-2, Lela, Job1, 8

3, 2013-11-3, Fry, Job1, 6

4, 2013-11-3, Fry, Job2, 3

5, 2013-11-3, Lela, Job1, 10

6, 2013-11-4, Professor, Job1, 4

7, 2013-11-4, Professor, Job2, 2

8, 2013-11-5, Zoidberg, Job1, 8

The result of the above table would return 7 (5 missing hours from Bender on 2013-11-2 + 2 hours from the Professor on the 2013-11-4) in the text box. So in order to display the missing hours in a Month in a text box, I need to deduct the sum of hours per employee, per day, from 8 and then sum the answers.

I did try loading the hours users were working by using a cap for the Hours field (below) but this would only work if the Employee only had one entry for that day(ID5 from above table would result in Hours=8). Two entries would still return the wrong answer (ID3=6 & ID4=3 would still result in Hours for the day=9).

LOAD Date,

          EmployeeName,

          JobName,

          Hours,

          if(Hours>8, 8, Hours)

FROM TIME;

Thanks for taking the time to read this and, as always, any help would be greatly appreciated.

[swuehl · Accepted solution]

You can probably do it in the script using a GROUP BY and aggregation functions or in your chart by using advanced aggregation:

=sum( aggr( if(sum(Hours)>8,8, sum(Hours) ), EmployeeName, Date))

[ciaran_mcgowan]

Wow. that's great. Thank you so much for helping me out with this. I ended up going with your advanced aggregation approach in the text box and combining it with my original check to see how many hours there are in the month. This is how my final expression looks:

=num(

((

(COUNT({<WeekDay -= {"Sat","Sun"}>} WeekDay)*8)*(COUNT({<%Key_Emp -= {46}>} EMPLOYEE.Active))

-

(SUM( AGGR( IF(SUM({<WeekDay -= {"Sat","Sun"}>} TIME.Hours)>8, 8,

     SUM({<WeekDay -= {"Sat","Sun"}>} TIME.Hours)), TIME.EmployeeName, DisplayDate)))

)/8, '#,#0.00')

I also divided the answer by 8 so instead of showing how many hours are missing, it shows how many days are missing (rounded up to two decimal places). It's not pretty to look at but it gets the job done.

Thanks again,swuehl!