Help on efficiency calculation!

Anonymous — Thu, 15 Dec 2016 12:33:36 GMT

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.

Re: Help on efficiency calculation!

Michiel_QV_Fan — Thu, 15 Dec 2016 12:37:58 GMT

Untested, but can you try this:

AGGR(

NetWorkDays(MonthStart(min(Date)),MonthEnd(max(Date)),$(ListOfHolidays))*8)*count(Distinct(NAME)/SUM(UNIT PRODUCED)

, NAME)

topic Re: Help on efficiency calculation! in QlikView

Help on efficiency calculation!

Re: Help on efficiency calculation!