Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
kiran3733
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?

Can someone please help me with this.

Thanks in advance.

 

1 Reply
michielvandegoo
Valued Contributor

Re: Help on efficiency calculation!

Untested, but can you try this:

AGGR(

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

, NAME)