Hi,
I am looking for some help creating the correct syntax to calculate the "Working Days Available" based on calendar selections by an employee/ departmental basis.
Currently I am calculating this using the following expression inside a pivot table:
Sum(aggr(NetWorkDays(vMinCalendarDate, vMaxCalendarDate), [D Full Name], [E Full Name]))
This works as designed and for example Jan 2015 = 22 working days available. It also aggregates the sum of this across the employees and departments.
However I now need to expand on this logic to account for New Starts and Leavers. As an example lets says we have a employee starting on the 27th Jan, this would mean their true "Working Days Available" for Jan 2015 would only be 4 days.
I have managed to achieve the desired result by creating an if statement as follows:
if([E Hire Date] > vMinCalendarDate, Sum(aggr(NetWorkDays([E Hire Date], vMaxCalendarDate), [D Full Name], [E Full Name])),
Sum(aggr(NetWorkDays(vMinCalendarDate, vMaxCalendarDate), [D Full Name], [E Full Name]))
)
The problem I am faced with is the totalling/ aggregation of this within the pivot table. The above expression works for the individual employee however the aggregation across the department is incorrect. Please see example below of the results I am currently achieving in the pivot table for Jan 2015:
| Dept | Employee | Hire Date | Working Days Available |
|---|
| Dept 1 | Employee 1 | 12th Aug 2013 | 22 |
| Dept 1 | Employee 2 | 27th Jan 2015 | 4 |
| Dept 1 | Employee 3 | 3rd May 2010 | 22 |
| TOTAL | | | 66 |
As you can see the Total shows 66 working days available however the true result across Dept 1 should be 48 working days available.
Please can you help?
Thanks,
Nikki
