Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Network Days - Working Days Available (Starters & Leavers)

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:

DeptEmployeeHire DateWorking Days Available
Dept 1Employee 112th Aug 201322
Dept 1Employee 227th Jan 20154
Dept 1Employee 33rd May 201022
TOTAL66

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

1 Reply
MVP & Luminary
MVP & Luminary

Re: Network Days - Working Days Available (Starters & Leavers)

Try something like this:

Sum(aggr(

     NetWorkDays(if([E Hire Date] > vMinCalendarDate, [E Hire Date], vMinCalendarDate), vMaxCalendarDate),      [Employee]))

- Marcus