I need to calculate the "net working days" of employees. The data comes from our punch clock system, users can punch-in and punch-out several times per day - so we can get multiple data records for the same day. The definition for "net working days" would be to count the number of days for which we have any time reports, not counting days, where the employee was either on vacation, on sick leave or had any kind of other absences. Also, days, where e.g. the user had a medical appointment before going to work should not count. All mentioned absences are also listed as records in the same table. I have already created a field where any record which is an absence, receives the count 1, intending to count the number of days where the sum of this field is zero. But I fail to get a total count of days when I do not use the "Date" Dimension in my report.
Here is the expression I tried:
if(aggr(sum(AbsenceCount), Date, User) = '0', count(DISTINCT(Date)))
Below an example: what I would like to get back is the count 2 - for two days within the selected date range which are net working days for the user.
| User | Date | Time | Record Type | AbsenceCount |
|---|
| John Doe | 25-April-2014 | 08:00AM | Work | 0 |
| John Doe | 25-April-2014 | 02:00PM | Work | 0 |
| John Doe | 26-April-2014 | 08:00AM | Medical Appointment | 1 |
| John Doe | 26-April-2014 | 02:00PM | Work | 0 |
| John Doe | 27-April-2014 | 08:00AM | Work | 0 |
