topic Re: Comparing one Employee's Productivity to Everyone Else's in QlikView

Comparing one Employee's Productivity to Everyone Else's

proctors — Wed, 25 Nov 2020 16:16:04 GMT

So we are trying to calculate an employees productivity, which is measured in RVU units to that of their own employee type. Now, it should also be noted that there are different types of employees, so we would also want them to be compared only to other employees of their type.

Here is the expression I've tried, but I cannot get it to show any data.

Used Dimensions for Bar Chart = [Employee Name]

Employee's Own Productivity =Sum([RVU WORK]) (This one works)

Other Employee's Productivity of the same Type (excluding the currently selected employee)

=avg(aggr(sum({<[Employee Name]-=[Employee Name]>}[RVU WORK]),[Employee Name], [Employee Type]))

Re: Comparing one Employee's Productivity to Everyone Else's

vvira1316 — Fri, 08 Sep 2017 15:59:57 GMT

Any Sample data may help.

Also in your expression

=avg(aggr(sum({<[Employee Name]-=[Employee Name]>}[RVU WORK]),[Employee Name], [Employee Type]))

will not only exclude selected employee but all of them

Re: Comparing one Employee's Productivity to Everyone Else's

vishsaggi — Fri, 08 Sep 2017 16:31:37 GMT

May be try this?

=avg(aggr(sum({< [Employee Name] = P({1-$} [Employee Name]) >}[RVU WORK]),[Employee Name], [Employee Type]))

Re: Comparing one Employee's Productivity to Everyone Else's

proctors — Fri, 08 Sep 2017 17:08:08 GMT

Here is some cleaned data, but has the same structure as my tables.

Thus, I want to be able to compare A's sum to the average of B and C's summed data, and not D's sum RVU work.

Employee Name	FY MonthYear	RVU Work	Employee Type
A	Jul-2017	99	Psychologist
A	Aug-2017	135	Psychologist
A	Sep-2017	128.54	Psychologist
A	Oct-2017	158.62	Psychologist
A	Nov-2017	111.85	Psychologist
A	Dec-2017	146.43	Psychologist
A	Jan-2017	122.4	Psychologist
A	Feb-2017	133.13	Psychologist
A	Mar-2017	147	Psychologist
A	Apr-2017	81.63	Psychologist
A	May-2017	80.5	Psychologist
A	Jun-2017	100.4	Psychologist
B	Jul-2017	176.9	Psychologist
B	Aug-2017	232.26	Psychologist
B	Sep-2017	170.5	Psychologist
B	Oct-2017	87.5	Psychologist
B	Nov-2017	157.22	Psychologist
B	Dec-2017	129.56	Psychologist
B	Jan-2017	146.26	Psychologist
B	Feb-2017	157.3	Psychologist
B	Mar-2017	156.5	Psychologist
B	Apr-2017	153.5	Psychologist
B	May-2017	196.26	Psychologist
B	Jun-2017	126.9	Psychologist
C	Jul-2017	73.5	Psychologist
C	Aug-2017	86.4	Psychologist
C	Sep-2017	110	Psychologist
C	Oct-2017	133.5	Psychologist
C	Nov-2017	130	Psychologist
C	Dec-2017	113.5	Psychologist
C	Jan-2017	128	Psychologist
C	Feb-2017	157	Psychologist
C	Mar-2017	150.5	Psychologist
C	Apr-2017	127	Psychologist
C	May-2017	122.4	Psychologist
C	Jun-2017	149	Psychologist
D	Jul-2017	229.6	Fellow
D	Aug-2017	158.6	Fellow
D	Sep-2017	166.12	Fellow
D	Oct-2017	185.74	Fellow
D	Nov-2017	163.54	Fellow
D	Dec-2017	162.7	Fellow
D	Jan-2017	168.5	Fellow
D	Feb-2017	187.4	Fellow
D	Mar-2017	166.5	Fellow
D	Apr-2017	222.2	Fellow
D	May-2017	200.5	Fellow
D	Jun-2017	93.2	Fellow

Re: Comparing one Employee's Productivity to Everyone Else's

proctors — Fri, 08 Sep 2017 17:10:53 GMT

Not working, coming up blank.

Re: Comparing one Employee's Productivity to Everyone Else's

vishsaggi — Fri, 08 Sep 2017 17:16:28 GMT

What is your expected output out of this data. What are your selections i mean when you select and Employee what should be your expected out put to be and where you want to see this ? In a table or in a text box?

Re: Comparing one Employee's Productivity to Everyone Else's

proctors — Fri, 08 Sep 2017 17:20:12 GMT

So I select Employee A from my list box.

In my bar graph, I have the sum of A's RVU in column 1, then the average of B and C's data in a second column. This way we can do a side-by-side comparison.

Re: Comparing one Employee's Productivity to Everyone Else's

vishsaggi — Fri, 08 Sep 2017 17:54:04 GMT

Ok you said in your initial post like below

"Other Employee's Productivity of the same Type (excluding the currently selected employee)"

So if you select A from the Employee Listbox you mean exclude A ? Can you clarify please?

Re: Comparing one Employee's Productivity to Everyone Else's

swuehl — Fri, 08 Sep 2017 18:00:32 GMT

Maybe something like

=avg({<[Employee Name] = e(), [Employee Type] = p()>}

aggr(

sum({<[Employee Name] = e(), [Employee Type] = p()>} [RVU WORK])

,[Employee Name])

)

Re: Comparing one Employee's Productivity to Everyone Else's

vvira1316 — Fri, 08 Sep 2017 18:00:33 GMT

Hi,

Will there be always one employee selected or more than one/All by each type etc. That will impact calculation. Please advise.

This is what I've got so far but want to wait till you provide additional info..

Re: Comparing one Employee's Productivity to Everyone Else's

proctors — Fri, 08 Sep 2017 18:28:45 GMT

Yes, If I select A, I still want A's data in Column 1, but Column two should exclude their data.

Re: Comparing one Employee's Productivity to Everyone Else's

proctors — Fri, 08 Sep 2017 18:37:32 GMT

This one produced data, but instead gave me a bar line for each employee's data instead of the average of them all.

The blue line is the employee I selected, the reds are each employee's averages. How can I collapse all of theirs into one column?

Re: Comparing one Employee's Productivity to Everyone Else's

swuehl — Fri, 08 Sep 2017 18:43:36 GMT

You are using employee as dimension, right?

Try using no dimension, but the two expressions only.

Re: Comparing one Employee's Productivity to Everyone Else's

vvira1316 — Fri, 08 Sep 2017 18:51:46 GMT

=Avg({<[FY MonthYear],[Employee Type]>}Aggr(Avg({<[Employee Name]=e()>}[RVU Work]),[FY MonthYear],[Employee Type]))

gives me what you are looking for but it will give only when something is selected

Re: Comparing one Employee's Productivity to Everyone Else's

vvira1316 — Fri, 08 Sep 2017 19:11:58 GMT

Hi,

I don't think you will be able to have that functionality in the same chart. You will have to separate those expressions in different charts.

I've expanded data set to try out... I think it is working the way you are looking for....

Please see attached....

Best Regards,

Vijay

Re: Comparing one Employee's Productivity to Everyone Else's

proctors — Fri, 08 Sep 2017 19:51:05 GMT

Somehow this worked! Thanks.