# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
New Contributor

## Help on efficiency calculation!

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?

Tags (5)