Myself a qlik view beginner.
In the current scenario, my requirement is to calculate hours/unit produced by individual team member and hours/unit produced by team.
Please refer the below table for Employee Name, Units produced, Hours consumed and year.
NAME | UNITS PRODUCED | HOURS CONSUMED | YEAR |
ANDY | 1800 | 1992 | 2014 |
JAKE | 1500 | 1992 | 2014 |
SAM | 1650 | 1992 | 2014 |
JOHN | 1700 | 1992 | 2014 |
ANDY | 1300 | 1992 | 2015 |
JAKE | 1400 | 1992 | 2015 |
SAM | 1650 | 1992 | 2015 |
JOHN | 500 | 816 | 2015-May(left company) |
BURT | 600 | 1168 | 2016-(worked from January to July) |
ANDY | 1300 | 1992 | 2016 |
JAKE | 1400 | 1992 | 2016 |
SAM | 1650 | 1992 | 2016 |
Formula I have used in qlikview is
=NetWorkDays(MonthStart(min(Date)),MonthEnd(max(Date)),$(ListOfHolidays))*8)*count(Distinct(NAME)/SUM(UNIT PRODUCED)
NOTE1: 8- Working hours per day.
NOTE2: Month-wise table will result in huge data, therefore i have clubbed the hours consumed year-wise.
For year 2016, Burt has worked only for 7 months. If I click on his name I get answer correctly since count(distinct) eliminates all duplicates and choose his value as 1.
Problem appears when I employ the same formula for the complete team in the year 2016 and 2015. In reference with the above table we see that Burt has left the company in July 2016 which results in 3.6 team members for the year 2016. Similarly for the year 2015 when John left the company team members for the year 2015 is 3.54.
Count(Distinct) works well if the team members are available throughout the year, but my pain point is, what happens when one of employee leaves the company in between the year?
Can someone please help me with this.
Thanks in advance.
