Problem with weighting scores on a Scorecard

[#1]

Hi All,

Once again I need to turn to my Qlikview colleagues for a nudge in the right direction. As usual all help very gratefully received.

My problem is that I want to look at the amount of work done by users & weight it by the  average amount of time taken for the job by all users.

Consider the following data - for simplicity I have two users Joe & Fred who do two different jobs A & B

during the day they performed the following & I want to scorecard them to see who is working most efficiently.

person	job type	time taken
Joe	A	0.5
Joe	A	0.6
Joe	A	0.7
Joe	B	0.8
Joe	B	0.5
Fred	B	0.9
Fred	A	0.8

In Excel I can display the summary data as follows.

Note for column e  I say that for type A the average is 0.5+0.6+0.7+0.8 /4 = 0.65 & for type B the average is 0.5+0.8+0.9/3 = 0.73

and then apply it to the quantites to work out a deviation from the average

a	b	c	d	e	f	g
person	type	total time taken	no of jobs	average by type	d*e	efficiency score = f-c
Fred	A	0.8	1	0.65	0.65	-0.15
Fred	B	0.9	1	0.73	0.73	-0.17
Joe	A	1.8	3	0.65	1.95	0.15
Joe	B	1.3	2	0.73	1.46	0.16

I have nearly got a formulae to work in Qlik - but it will only work for one user !!!!

my column e equivalent in Qlik displays 0.65 for Joe "A" & 0.73 for Joe "B" but "-" both times for Fred

Person & Type in my chart are Dimensions (in that order) & the expression average score is

(aggr(Sum(time taken),Type)/aggr(Count(Case),Type))*Count(Case)

[swuehl]

Try a NODISTINCT qualifier with the aggr() function:

=(aggr(NODISTINCT Sum(time taken) / Count(Case),Type))*Count(Case)

edit: It's probably even easier to use

=sum(TOTAL<[job type]> [time taken]) / count(TOTAL<[job type]> Case) * count(Case)

[#3 · Accepted solution]

Hi,

You could also try this expressions,

For column E

aggr(nodistinct avg([time taken]), [job type])

For column F

count([job type])*aggr(nodistinct avg([time taken]), [job type])

For column G

count([job type])*aggr(nodistinct avg([time taken]), [job type]) - sum([time taken])

Regards,

Janzen

[#4]

Swuehl - your answer indicated I was on the right track but did not fix the problem.

Janzen - Spot on.  However once again shows that as good as qlikview is (& I think its really good) sometimes

the syntax can get very complex !!

[swuehl]

Right, the syntax can get very complex, the learning curve might get a little steep, so good luck and keep on going. But this is due to the fact that QV's syntax is really powerful (but unfortunately also due to the fact that it is not always as consistent and easy as possible).

May I ask why my above expression haven't fixed the issue? I do get the numbers from your result table, column f when applying the expression to your sample data (well, you haven't provided Case field, so I used actually just person).

Using avg() for the average is of course a good idea, so I would do it like this without any advanced aggregation:

=avg(TOTAL<[job type]> [time taken]) * count(person) - sum([time taken])

[#6]

I have attached my original test qvw for you to look at. when i added your code it made no change for me.

However for a bonus 5 points the one extra thing I would like to do is to produce a subtotal by person of their net deviation from the average.so using the code above Fred would have a total displayed of -0.32 & joe would show 0.31.

Janzens reply works perfectly for each line but I cannot get it to subtotal.

[swuehl]

I am just curious why my expressions seemed to not be working while others (which look to me essentially the same) seem to work.

After copying my expressions into your sample file, I am still unsure, the results look ok to me.

Besides this, I think using the solution with no advanced aggregation, i.e. using the TOTAL qualifier, should solve your subtotal issue.

Have a nice evening,

Stefan